Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Null values for Integer/Decimal datatype in Phoenix Table

Highlighted

Null values for Integer/Decimal datatype in Phoenix Table

Hi, I am creating Phoenix table with few integer/decimal column has been defined as Null but when we are getting data with null values in these columns then these records are getting rejected out. Null value in the varchar , char & Date datatype are getting loaded as expected. Field Defination are like this ; FAILED_RECORD_IND INTEGER NULL Can we not store Null value in the integer/decimal columns in the Phoenix ? If we can, then what change we need to make it our end.

2 REPLIES 2
Highlighted

Re: Null values for Integer/Decimal datatype in Phoenix Table

Super Collaborator

It's not clear what do you mean by rejected. There should be no problems with getting null values for any data types. Could you please provide the code snippet how you get it?

Highlighted

Re: Null values for Integer/Decimal datatype in Phoenix Table

Thanks @Sergey for your quick response. I am getting the following error when i am trying to insert data into Phoenix table :

LF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/usr/hdp/2.6.0.3-8/phoenix/phoenix-4.7.0.2.6.0.3-8-client.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/hdp/2.6.0.3-8/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. 18/02/07 11:35:46 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable 18/02/07 11:35:47 WARN shortcircuit.DomainSocketFactory: The short-circuit local reads feature cannot be used because libhadoop cannot be loaded. csv columns from database. 18/02/07 11:35:48 ERROR util.CSVCommonsLoader: Error upserting record [OXF, 000000000000000000000000000000000001249464901 1249464901OX, KS13648, 0000165069300004133683, UDW, 1548760225, 154169099, 1542848652, 154127302277, CDB, OXF, OXF, A, B, 20151201, 20160131, CJ, 20151201, EE, , , 1249464901, , , A, 20171212 19:07:03, , , , , , 1, 20171212 19:07:03] 18/02/07 11:35:48 ERROR util.CSVCommonsLoader: Error upserting record [OXF, 000000000000000000000000000000000001249474501 1249474501OX, WW1154, 0000105801900004254463, UDW, 1548760421, 154140907, 1542951884, 154127303436, CDB, OXF, OXF, A, B, 20151115, 20160131, CG, 20151115, EE, , , 1249474501, , , A, 20171212 19:07:03, , , , , , 2, 20171212 19:07:03] 18/02/07 11:35:48 ERROR util.CSVCommonsLoader: Error upserting record [OXF, 000000000000000000000000000000000001249477701 1249477701OX, CC53112, 0000185758700004704901, UDW, 1548760485, 154407615, 1543338985, 154133127733, CDB, OXF, OXF, A, RX, 20161201, 20171130, , 20151201, EE, , , 1249477701, , , A, 20171212 19:07:03, , , , , , 3, 20171212 19:07:03] 18/02/07 11:35:48 ERROR util.CSVCommonsLoader: Error upserting record [OXF, 000000000000000000000000000000000001249489201 1249489201OX, MA24946, 0000185745800004458269, UDW, 1548760700, 154407649, 1543122691, 154127305062, CDB, OXF, OXF, A, RX, 20151201, 20160630, ZH, 20151201, EE, , , 1249489201, , , A, 20171212 19:07:03, , , , , , 4, 20171212 19:07:03] 18/02/07 11:35:48 ERROR util.CSVCommonsLoader: Error upserting record [OXF, 000000000000000000000000000000000001249490802 1249490801OX, AC37035, 0000185731500004456823, UDW, 1548760718, 154407643, 1543123980, 154127305149, CDB, OXF, OXF, A, B, 20151201, 20161130, , 20151201, SP, , , 1249490801, , , A, 20171212 19:07:03, , , , , , 5, 20171212 19:07:03] 18/02/07 11:35:48 ERROR util.CSVCommonsLoader: Error upserting record [OXF, 000000000000000000000000000000000001249492201 1249492201OX, DP13303, 0000185717900004461756, UDW, 1548760733, 154408199, 1543128398, 154127305223, CDB, OXF, OXF, A, B, 20151201, 20161130, ZH, 20151201, EE, , , 1249492201, , , A, 20171212 19:07:03, , , , , , 6, 20171212 19:07:03] 18/02/07 11:35:48 ERROR util.CSVCommonsLoader: Error upserting record [OXF, 000000000000000000000000000000000001249495801 1249495801OX, MD15763, 0000185617300004715786, UDW, 1548760778, 154407634, 1543329802, 154133128456, CDB, OXF, OXF, A, B, 20161201, 20170831, ZH, 20151201, EE, , , 1249495801, , , A, 20171212 19:07:03, , , , , , 7, 20171212 19:07:03] 18/02/07 11:35:48 ERROR util.CSVCommonsLoader: Error upserting record [OXF, 000000000000000000000000000000000001249496601 1249496601OX, MD15763, 0000185617300004456843, UDW, 1548760786, 154407634, 1543122607, 154127305458, CDB, OXF, OXF, A, M, 20151201, 20161017, ZH, 20151201, EE, , , 1249496601, , , A, 20171212 19:07:03, , , , , , 8, 20171212 19:07:03] 18/02/07 11:35:48 ERROR util.CSVCommonsLoader: Error upserting record [OXF, 000000000000000000000000000000000001249500103 1249500101OX, PW00101, 0000001311600004390829, UDW, 1548760854, 154175639, 1543065155, 154127305729, CDB, OXF, OXF, A, B, 20160101, 20160920, ZH, 20160101, CH, , , 1249500101, , , A, 20171212 19:07:03, , , , , , 9, 20171212 19:07:03] 18/02/07 11:35:48 ERROR util.CSVCommonsLoader: Error upserting record [OXF, 000000000000000000000000000000000001249507003 1249507001OX, PW00101, 0000001311600004390829, UDW, 1548760995, 154175639, 1543065155, 154127306320, CDB, OXF, OXF, A, M, 20160101, 20161231, ZH, 20160101, CH, , , 1249507001, , , A, 20171212 19:07:03, , , , , , 10, 20171212 19:07:03] CSV Upsert complete. 0 rows upserted..

This is the table structure i defined in the Phoenix :

create table if not exists POC.DSO_375168_352817_temp ( mbr_org_sys_src_cd VARCHAR(10) NULL , org_sys_mbr_idntfr VARCHAR(100) NULL , org_sys_custmr_policy_idntfr VARCHAR(100) NULL , org_sys_custmr_grp_plan_idntfr VARCHAR(100) NULL , prvdng_sys_src_cd VARCHAR(10) NULL , prvdng_sys_membr_idntfr VARCHAR(20) NULL , prvdng_sys_policy_idntfr VARCHAR(19) NULL , prvdng_sys_cust_grp_plan_idntfr VARCHAR(19) NULL , prvdng_sys_mbr_coverage_idntfr VARCHAR(20) NULL , mbr_src_sys_cd VARCHAR(10) NULL , platform_source_cd VARCHAR(10) NULL , policy_org_src_sys_cd VARCHAR(10) NULL , employee_status_sc VARCHAR(10) NULL , coverge_type_cd VARCHAR(5) NULL , mbr_cov_effvt_date VARCHAR(10) NULL , mbr_cov_term_date VARCHAR(10) NULL , mbr_cov_term_reason VARCHAR(10) NULL , org_mbr_effvt_date VARCHAR(10) NULL , subscrbr_relationship_cd VARCHAR(10) NULL , source_share_arngmnt_cd VARCHAR(5) NULL , source_obliger_cd VARCHAR(5) NULL , uhgei_subscrbr_id VARCHAR(50) NULL , plan_variat_subdiv_cd VARCHAR(4) NULL , report_cd_branch_cd VARCHAR(4) NULL , logical_delete_flag VARCHAR(5) NULL , extract_timestamp VARCHAR(17) NULL , FAILED_RECORD_IND INTEGER NULL , FAILED_RECORD_REASON VARCHAR(4000) NULL , fp_record_identifier VARCHAR(10) NULL , fp_layout_identifier VARCHAR(10) NULL , fp_file_identifier VARCHAR(10) NULL , recNdx INTEGER NOT NULL , base_record VARCHAR(4000) NULL , job_extract_timestamp VARCHAR(17) NULL CONSTRAINT id_pk PRIMARY KEY (recNdx)) ;

Don't have an account?
Coming from Hortonworks? Activate your account here