Member since
06-21-2017
15
Posts
1
Kudos Received
0
Solutions
11-01-2018
03:55 PM
Hi Team,
We recently saw that the regions count in our region server is increasing exponentially . We have 3 region server and average number of regions in these server are around 3000. Region count should be around 200-250 at max.
We are trying to find the root cause of this issue and how it can be avoided in future. One possible reason that i can think is that we are creating separate phoenix tables for each files we receive. We have requirement to create seperate table for each files so we can not avoid. We need to find the way that if we can tie up multiple tables in one region or any other better way to fix this issue. We are using following code to load Phoenix table : CsvBulkLoadTool csvBulkLoadTool = new CsvBulkLoadTool();
conf = HBaseConfiguration.create();
ConnectionData dt = PhoenixConnectionManager.getConnectionDataFromJNDI(phoenixJndi);
zkQuorum = dt.getQuorum(); // "rdalhdpmastd001.kbm1.loc,rdalhdpmastd002.kbm1.loc,rdalhdpmastd003.kbm1.loc"; conf = new Configuration(); conf.addResource(new Path(dt.getConfigDir() + "core-site.xml"));
conf.addResource(new Path(dt.getConfigDir() + "hbase-site.xml"));
conf.addResource(new Path(dt.getConfigDir() + "hdfs-site.xml"));
conf.addResource(new Path(dt.getConfigDir() + "yarn-site.xml"));
conf.addResource(new Path(dt.getConfigDir() + "mapred-site.xml")); conf.set("hadoop.security.authentication", "Kerberos");
conf.set("http://mapreduce.framework.name", "yarn");
// conf.set("phoenix.mapreduce.import.fielddelimiter",delimiter);
UserGroupInformation.setConfiguration(conf);
UserGroupInformation.loginUserFromKeytab(dt.getPrincipal(), dt.getConfigDir() + dt.getTabFile()); conf.set("hadoop.security.authentication", "Kerberos");
conf.set("hadoop.home.dir", "/opt/payara41");
conf.set("http://mapreduce.framework.name", "yarn");
if (delimiter != null && !delimiter.isEmpty()) {
conf.set("phoenix.mapreduce.import.fielddelimiter", new String(delimiter.getBytes("UTF8"), "UTF8"));
} args.add("--input");
args.add(inputFileName);
// args.add("--delimiter"); args.add(delimiter);
args.add("--table");
args.add(parseTableNameForJustTable(targetTableName));
args.add("--schema");
args.add(parseTableNameForSchema(targetTableName));
args.add("--import-columns");
args.add(colNames);
args.add("--zookeeper");
args.add(zkQuorum); if (filePermLine != null && !filePermLine.isEmpty()) {
// not sure yet
} URL[] urls = ((URLClassLoader) (conf.getClassLoader())).getURLs();
for (URL pth : urls) {
System.out.println(pth.getPath());
} csvBulkLoadTool.setConf(conf);
exitCode = -1; exitCode = csvBulkLoadTool.run(args.toArray(new String[args.size()]));
... View more
Labels:
- Labels:
-
Apache HBase
-
Apache Phoenix
06-11-2018
05:01 PM
Hi, I have a requirement where i need to load all the columns of file in a single column in table. I am thinking to make newline as delimiter to insert all columns of file in a single column of Phoenix. I tried various options to pass newline character as delimiter but none of it worked so far : ./psql.py -d $'/n' -t POC.SINGLE_LINE /export/home/KBM_HOU/pkumar/test_newdelimiter.csv ./psql.py -d $'//n' -t POC.SINGLE_LINE /export/home/KBM_HOU/pkumar/test_newdelimiter.csv ./psql.py -d '/n' -t POC.SINGLE_LINE /export/home/KBM_HOU/pkumar/test_newdelimiter.csv ./psql.py -d '//n' -t POC.SINGLE_LINE /export/home/KBM_HOU/pkumar/test_newdelimiter.csv I am getting error of invalid single character. Exception in thread "main" java.lang.IllegalArgumentException: Invalid single character: '//n'
at org.apache.phoenix.util.PhoenixRuntime$ExecutionCommand.getCharacter(PhoenixRuntime.java:725)
at org.apache.phoenix.util.PhoenixRuntime$ExecutionCommand.parseArgs(PhoenixRuntime.java:655)
at org.apache.phoenix.util.PhoenixRuntime.main(PhoenixRuntime.java:215) Could you let me know how to pass the newline character as the delimiter for any unix file in Phoenix bulk load process.
... View more
Labels:
- Labels:
-
Apache HBase
-
Apache Phoenix
05-16-2018
08:55 PM
. We have been trying to load phoenix table remotely so just
wanted to check if we have any option where we can run the load process outside
of cluster. Currently we are being able to load the phoenix table if we
run the load process on cluster. Here are commands we are running to load the phoenix
table in Yarn mode : #!/bin/bash export
HADOOP_CLASSPATH=/usr/hdp/current/hbase-client/lib:/usr/hdp/current/hbase-client/conf hadoop
jar /usr/hdp/current/phoenix-client/phoenix-client.jar \ org.apache.phoenix.mapreduce.CsvBulkLoadTool
\ -Dfs.permissions.umask-mode=000
\ --table
POC.CIC_BULKTEST_20180212 \ --input
/poc/Raw_Zone/DSO_375168_352817.csv Command to load phoenix table in standalone mode : ./psql.py -t POC.DSO_22334808_25257_NEW
/export/home/KBM_HOU/pkumar/new_test_file.csv We are executing both these commands from cluster but we
need to find execute these load process from outside the cluster. How the
Phoenix table can be exposed to outside the cluster. We found one way to
connect it through JDBC connection but there is not any bulk load process using
jdbc connection. We are being able to do only the Upsert data through loop
process :
Connection con =
DriverManager.getConnection("jdbc:phoenix:[zookeeper]");
stmt = con.createStatement();
stmt.executeUpdate("create table test (mykey integer not null primary key,
mycolumn varchar)");
stmt.executeUpdate("upsert into test values (1,'Hello')");
stmt.executeUpdate("upsert into test values (2,'World!')"); con.commit();
... View more
Labels:
- Labels:
-
Apache HBase
-
Apache Phoenix
02-13-2018
02:10 PM
Thanks for your response Josh. i got the right directory for Jar file and executed command from there but still getting the issue. I followed all the steps provided in this help file : https://phoenix.apache.org/bulk_dataload.html Here is the location of jar file : /usr/hdp/2.6.0.3-8/phoenix First i ran this command to do the bulk loading via mapreduce : hadoop jar phoenix-4.7.0.2.6.0.3-8-client.jar org.apache.phoenix.mapreduce.CsvBulkLoadTool --table POC.DSO_375168_352817_MAP --input /poc/Raw_Zone/DSO_375168_352817.csv Then i tried HADOOP_CLASSPATH=/path/to/hbase-protocol.jar:/path/to/hbase/conf hadoop jar phoenix-4.7.0.2.6.0.3-8-client.jar org.apache.phoenix.mapreduce.CsvBulkLoadTool --table POC.DSO_375168_352817_MAP --input /poc/Raw_Zone/DSO_375168_352817.csv I even tried this command : hadoop jar phoenix-4.7.0.2.6.0.3-8-client.jar org.apache.phoenix.mapreduce.CsvBulkLoadTool -Dfs.permissions.umask-mode=000 --table POC.DSO_375168_352817_MAP --input /poc/Raw_Zone/DSO_375168_352817.csv I keep getting the same error : Caused by: org.apache.hadoop.hbase.client.RetriesExhaustedException: Can't get the locations
at org.apache.hadoop.hbase.client.RpcRetryingCallerWithReadReplicas.getRegionLocations(RpcRetryingCallerWithReadReplicas.java:312)
at org.apache.hadoop.hbase.client.ScannerCallableWithReplicas.call(ScannerCallableWithReplicas.java:156)
at org.apache.hadoop.hbase.client.ScannerCallableWithReplicas.call(ScannerCallableWithReplicas.java:60)
at org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithoutRetries(RpcRetryingCaller.java:200)
at org.apache.hadoop.hbase.client.ClientScanner.call(ClientScanner.java:327)
at org.apache.hadoop.hbase.client.ClientScanner.nextScanner(ClientScanner.java:302)
at org.apache.hadoop.hbase.client.ClientScanner.initializeScannerInConstruction(ClientScanner.java:167)
at org.apache.hadoop.hbase.client.ClientScanner.<init>(ClientScanner.java:162)
at org.apache.hadoop.hbase.client.HTable.getScanner(HTable.java:794)
at org.apache.hadoop.hbase.MetaTableAccessor.fullScan(MetaTableAccessor.java:602)
at org.apache.hadoop.hbase.MetaTableAccessor.tableExists(MetaTableAccessor.java:366)
at org.apache.hadoop.hbase.client.HBaseAdmin.tableExists(HBaseAdmin.java:411)
at org.apache.phoenix.query.ConnectionQueryServicesImpl$13.call(ConnectionQueryServicesImpl.java:2369)
... 21 more
18/02/12 10:24:43 INFO zookeeper.ClientCnxn: Opening socket connection to server localhost/127.0.0.1:2181. Will not attempt to authenticate using SASL (unknown error)
18/02/12 10:24:43 WARN zookeeper.ClientCnxn: Session 0x0 for server null, unexpected error, closing socket connection and attempting reconnect
java.net.ConnectException: Connection refused
at sun.nio.ch.SocketChannelImpl.checkConnect(Native Method)
at sun.nio.ch.SocketChannelImpl.finishConnect(SocketChannelImpl.java:717)
at org.apache.phoenix.shaded.org.apache.zookeeper.ClientCnxnSocketNIO.doTransport(ClientCnxnSocketNIO.java:361)
at org.apache.phoenix.shaded.org.apache.zookeeper.ClientCnxn$SendThread.run(ClientCnxn.java:1125)
18/02/12 10:24:43 INFO zookeeper.ClientCnxn: Opening socket connection to server 30.127.0.0/30.127.0.0:2181. Will not attempt to authenticate using SASL (unknown error) Thanks once again for all your help @Josh Elser
... View more
02-08-2018
07:35 PM
Hi , I am getting the following error when i am trying to load the phoenix table through map reduce : Not a valid JAR: /usr/hdp/2.6.0.3-8/phoenix/bin/phoenix-4.7.0.2.6.0.3-8-client.jar I am running the following command : hadoop jar phoenix-4.7.0.2.6.0.3-8-client.jar org.apache.phoenix.mapreduce.CsvBulkLoadTool --table POC.DSO_375168_352817_MAP --input /export/home/KBM_HOU/pkumar/DSO_375168_352817.csv While debugging, i read it somewhere that there is some issue with phoenix 4 and above, so they recommended to use the following command : HADOOP_CLASSPATH=/path/to/hbase-protocol.jar:/path/to/hbase/conf hadoop jar phoenix-2.6.0.3-8-client.jar org.apache.phoenix.mapreduce.CsvBulkLoadTool --table POC.DSO_375168_352817_MAP --input /export/home/KBM_HOU/pkumar/DSO_375168_352817.csv But I am getting the same error.
... View more
Labels:
- Labels:
-
Apache Phoenix
02-07-2018
07:04 PM
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)) ;
... View more
02-07-2018
06:01 PM
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.
... View more
Labels:
- Labels:
-
Apache HBase
-
Apache Phoenix
07-27-2017
04:42 PM
1 Kudo
Hi Team, I am getting the following error message when i am trying to access Phoenix tables from the Zeppelin . I am sending my configuration setting in the attachment below . I went through the forum about this issue and implemented various suggestions provided but none of them being able to resolve this issue : phoenix-config.pngphoenix-artifacts.png %jdbc(phoenix) select * from POC.VALIDATION_RULE org.apache.zeppelin.interpreter.InterpreterException: null
java.sql.SQLException: org.apache.hadoop.hbase.client.RetriesExhaustedException: Failed after attempts=1, exceptions:
Thu Jul 27 10:50:37 CDT 2017, RpcRetryingCaller{globalStartTime=1501170637835, pause=100, retries=1}, java.io.IOException: Broken pipe
at org.apache.zeppelin.jdbc.JDBCInterpreter.getConnection(JDBCInterpreter.java:416)
at org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:564)
at org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:692)
at org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:94)
at org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:489)
at org.apache.zeppelin.scheduler.Job.run(Job.java:175)
at org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
... View more
Labels:
- Labels:
-
Apache Phoenix
-
Apache Zeppelin
06-26-2017
02:57 PM
Can i get any comment on this issue ? I need to finalize our design and I am waiting for expert feedback here. Thanks in advance.
... View more
06-22-2017
05:56 PM
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 ; ACTIVE_IND INTEGER 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.
... View more
Labels:
- Labels:
-
Apache HBase
-
Apache Phoenix
06-21-2017
04:01 PM
Thanks @Josh Elser. I took out the default part and created table. Now I am trying to import data into these table. I have .csv file with tab separated fields. I am getting issues in those columns where there are values with blank in it . for example, one of column which contains the validation logic has value like : (substring(:col(), 1, 2) != '02' and
substring(:col(), 3, 2) in ('29', '30') or
substring(:col(), 1, 2) in I am running this command to import these csv files into Table : ./psql.py -d $'\t' -t POC.STATSTC_TYPE /export/home/KBM_HOU/pkumar/test_data.csv I am being able to import those rows which does not have this type of validation logic with blank space. Do we need to pass any other argument to ignore those blank spaces in the column values ?
... View more
06-21-2017
02:58 PM
Hi, We need to migrate our existing Oracle tables to Hbase tables using the phoenix query services. We are using the 'create statement' in the Phoenix query services to create these tables. For few of our oracle tables, there are some columns which have the default values. These default values are either constant like 0 or variable like 'SYSDATE'. For example, Oracle table values are like this : ACTIVE_IND INTEGER DEFAULT 0 CREATED_DTTM DATE DEFAULT sysdate When I am trying to use these default statement in the Phoenix query services , I am getting the following errors : 08:52:16 [CREATE - 0 rows, 0.000 secs] [Code: 602, SQL State: 42P00] ERROR 602 (42P00): Syntax error. Missing "RPAREN" at line 2, column 169.
... 1 statement(s) executed, 0 rows affected, exec/fetch time: 0.000/0.000 sec [0 successful, 1 errors] Can we pass Default values in the Phoenix query services. If yes, then how can we do that ?
... View more
Labels:
- Labels:
-
Apache HBase
-
Apache Phoenix
