0

I am creating a table (say table1) in Big SQL, and loading the data from HDFS into table1. Now from this table1, I need to to load data to another table say table2, based on certain conditions and on daily basis append more data into this table2. Daily new data will be loaded into table1 and correspondingly new data should come into table2 as well.

I have tried the following ways

First

insert append into table table2 as select uri,localtimestamp,count(*) from table1 group by uri order by uri LIMIT 100;

SQL Exception(s) Encountered: [State: 42601][Code: -104]: Error parsing:
insert append into table table2 as select uri,localtimestamp,count(*) from table1 group by uri order by uri LIMIT 100;

Second

insert into table table2 as select uri,localtimestamp,count(*) from table1 group by uri order by uri LIMIT 100;

SQL Exception(s) Encountered: [State: 58004][Code: 15]: BIGSQL-GEN-0010 Found an internal error:
'Failed to execute query 'insert into table table2 as select uri,localtimestamp,count(*) from table1 group by uri order by uri LIMIT 100;': expected keyword values'.

Third

create table if not exists table2(URL_NAME,TODAY_DATE,COUNT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' as select uri,localtimestamp,count(*) from table1 where request_timestamp=localtimestamp group by uri order by uri LIMIT 100;

In this case, everyday, new table will be created, while I want the older data to be retained and new data to be added.

Fourth

Create table table2

CREATE EXTERNAL TABLE table2 (URL_NAME VARCHAR(500),DATE varchar(50),COUNT INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
0 rows affected (total: 0.22s)

insert overwrite table table2 select uri,localtimestamp,count(*) from table1 group by uri order by uri LIMIT 100;

SQL Exception(s) Encountered:
[State: 42601][Code: -104]: Error parsing:

<query>insert overwrite table table2 select uri,localtimestamp,count(*) from table1 group by uri order by uri LIMIT 100;</query> Expecting token <into> after token <insert>

Fifth

Load from sql query 'select uri, request_timestamp,1 from table1 where $conditions' split column uri into table table2;

SQL Exception(s) Encountered:
[State: 58004][Code: 15]: BIGSQL-GEN-0010 Found an internal error:

'Failed to execute query 'Load from sql query 'select uri, request_timestamp,1 from table1 where $conditions' split column uri into table table2': parse error: keyword hbase or hive expected'.

And if I run with keywords hive

SQL Exception(s) Encountered:
[State: 58004][Code: 15]: BIGSQL-GEN-0010 Found an internal error:

'Failed to execute query 'Load hive from sql query 'select uri, request_timestamp,1 from table1 where $conditions' split column uri into table table2'`: originating expression ends at (line: 1, column: 143): The statement failed because of an error in the Hive MetaStore. Hadoop log entry identifier: "[4d4e59269]"": com.ibm.biginsights.catalog.translator.hive.HiveExceptionTranslator$HiveNestedException: FAILED: ParseException line 1:5 mismatched input 'from' expecting DATA near 'load' in load statement

Any idea how to use INSERT INTO statement or how to load data from table to another using IBM BigSQL (version 1)

Update

I tried with LOAD as well but getting excpetions

LOAD FROM SQL QUERY 'select t1.uri, t1.request_timestamp,t1.cell_lac from sample.web3 t1 where $conditions'  split column t1.uri into table sample.u2_table;

SQL Exception(s) Encountered: [State: 58004][Code: 15]: BIGSQL-GEN-0010 Found an internal error: 'Failed to execute query 'LOAD FROM SQL QUERY 'select t1.uri, t1. request_timestamp,t1.cell_lac from sample.web3 t1 where $conditions' split column t1.uri into table sample.u2_table': parse error: keyword hbase or hive expected'.

LOAD FROM SQL QUERY 'select t1.uri, t1.request_timestamp,t1.cell_lac from sample.web3 t1 where $conditions' split column t1.uri into hive table sample.u2_table;

SQL Exception(s) Encountered: [State: 58004][Code: 15]: BIGSQL-GEN-0010 Found an internal error: 'Failed to execute query 'LOAD FROM SQL QUERY 'select t1.uri, t1.request_timestamp,t1.cell_lac from sample.web3 t1 where $conditions' split column t1.uri into hive table sample.u2_table': parse error: keyword hbase or hive expected'.

LOAD FROM TABLE sample.web3 COLUMNS (uri,request_timestamp, cell_lac) INTO hive TABLE sample.u2_table APPEND WITH LOAD PROPERTIES (num.map.tasks = 1);

SQL Exception(s) Encountered: [State: 58004][Code: 15]: BIGSQL-GEN-0010 Found an internal error: 'Failed to execute query 'LOAD FROM TABLE sample.web3 COLUMNS (uri,request_timestamp, cell_lac) INTO hive TABLE sample.u2_table APPEND WITH LOAD PROPERTIES (num.map.tasks = 1)': parse error: keyword hbase or hive expected'.

Raunak Kathuria
  • 3,185
  • 1
  • 18
  • 27

1 Answers1

0

I think, you can't use INSERT in that way:

The INSERT statement inserts a single row into an IBM® Big SQL HBase table.

And

The LOAD statements load data into a table. You can load from a remote data source into HBase or Hive. You can load from your cluster or local files into HBase or Hive.

When you want to use local file as using HIVE DATA at first you need to use EXPORT TO.

Your fifth solution is that you want and it should improved:

LOAD FROM SQL QUERY 'select uri, request_timestamp,1 from table1 where $conditions' split column uri into table table2;

The columns in the select clause must match both the names and the types of the columns in the target table definition.
Otherwise, you must specify the column.names property to specify the mapping to the target columns.

  • 1 has no name
  • use an alias for your table1 like this: ... 'select t1.uri, ... from table1 t1 ... ' SPLIT COLUMN t1.uri ...
  • In another way you can use LOAD FROM TABLE table1 COLUMNS (uri,request_timestamp, ...) INTO hbase TABLE table2 APPEND WITH LOAD PROPERTIES (num.map.tasks = 1)
shA.t
  • 16,580
  • 5
  • 54
  • 111