How to pass Default value in the Create Statement of Phoenix Query services ?

[#1]

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 ?

[#2 · Accepted solution]

This grammar was added in Apache Phoenix-4.9.0 but does not yet exist in the version of Phoenix bundled in an HDP release.

https://issues.apache.org/jira/browse/PHOENIX-476

[#3]

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 ?