#10855: Schema Script Generator produces INSERT statements which wrong for HIVE

slavakiev reported 2013-11-20T20:37:27Z · last modified 2014-11-04T17:54:58Z

Schema Script Generator produces INSERT statements which wrong for HIVE

Dev	tomconrad
QA	anilkalshetti

Priority	Low
Complexity	Unknown

Component	Hive
Version	16.0

Tested in Aqua Data Studio 14.0.3-14 Build #: 34647 on Ubuntu 12.04( Hive 0.12.0( localhost,embeded mode), Hadoop 0.23.9)

There is no option 'Generate Insert statements' in Schema Script Generator for getting traditional SQL INSERT and this is correct.

But despite this it produces INSERT... VALUES for tables with data which is wrong for HIVE( see attached screenshot)

6 attachments

hive_insert_SSG.JPG

2013-11-20T20:37:27Z

33 KB
hive_exportinsert.JPG

2013-11-20T21:10:16Z

45 KB
numbers.txt

2014-05-06T15:45:00Z

5 B
Apache-ADS14012.png

2014-05-07T04:21:07Z

226 KB
dev334ApacheHive013.png

2014-06-23T14:52:52Z

283 KB
dev334HDP21Hive013.png

2014-06-23T14:52:52Z

301 KB

All Comments (11) Change History

slavakiev 2013-11-20T21:09:44Z

Same problem is in Export with option Format:INSERT statements(see attached screenshot)

tomconrad 2014-05-01T16:13:26Z

Hi Slava,

I don't see any INSERT statements in the output from Schema Script Generator? Would you retest with the latest version of ADS?

Thanks,

Tom

tariqrahiman 2014-05-05T23:09:19Z

@anil: can you test this ? I was not able to reproduce in Cloudera.

anilkalshetti 2014-05-06T12:55:53Z · (edited)

Yes, I'm able to reproduce this issue for Distribution: Apache

a] [Apache Hadoop2.4, Hive 0.13, Distribution: Apache, Connection Type: Hiveserver1]
Please use below script:

CREATE TABLE default.numbers (

user_id bigint,

firstid tinyint,

lastid tinyint)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

TBLPROPERTIES ('COLUMN_STATS_ACCURATE'='true', 'numFiles'='2', 'numPartitions'='0', 'numRows'='0', 'rawDataSize'='0', 'totalSize'='12')

// First copy the file numbers.txt in ./hive/bin directory

LOAD DATA LOCAL INPATH 'numbers.txt' INTO TABLE numbers

SELECT * FROM numbers

GO

Please refer screenshot: Apache-ADS14012.png

b] [Cloudera CDH5, Hive 0.12]
Issue 11962 blocking to reproduce the issue at my end.
Please try below commands to reproduce at your end.

//Create Table

CREATE TABLE default.numbers (

user_id bigint,

firstid tinyint,

lastid tinyint)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

TBLPROPERTIES ('COLUMN_STATS_ACCURATE'='true', 'numFiles'='2', 'numPartitions'='0', 'numRows'='0', 'rawDataSize'='0', 'totalSize'='12')

// First need to copy the file numbers.txt

sudo cp '/home/cloudera/Desktop/numbers.txt' /var/run/cloudera-scm-agent/process/28-hive-HIVESERVER2/

cd /var/run/cloudera-scm-agent/process/28-hive-HIVESERVER2/

sudo chmod 777 ./numbers.txt

LOAD DATA LOCAL INPATH 'numbers.txt' INTO TABLE numbers

SELECT * FROM numbers

Then do the Export Data.

SachinPrakash 2014-06-22T23:31:46Z

@anil: #11962 is now resolved. Can you retest for Cloudera and post your results.

anilkalshetti 2014-06-23T14:51:19Z

Builds: ADS 15.0.0-dev-334
ADS 14.0.16

Tested this issue on below distributions:

//Distribution: Cloudera - Test Pass
1] Cloudera Quickstart VM CDH5 [Hive 0.12] , Distribution: Cloudera, Connection Type: Hiveserver2
The issue doesn't occur for both ADS 14.0.16 and ADS 15.0.0-dev-334 builds

//Distribution: Apache- Test Fail
2] Apache Hadoop2.4, Hive 0.13, Distribution: Apache, Connection Type: Hiveserver1
Issue is still observed.
Please refer screenshot: dev334ApacheHive013.png

//Distribution: Hortonworks- Test Fail
3] Hortonworks HDP2.1 Quickstart VM [Hive 0.13] , Distribution: Hortonworks, Connection Type: Hiveserver2
Issue is still observed.
Please refer screenshot: dev334HDP21Hive013.png

SachinPrakash 2014-06-23T15:32:08Z

@tom: lets fix this for HiveServer2

tomconrad 2014-07-03T15:10:17Z

Also ADS 14.0.17-5 - Fixed both Export and Schema Generator

anilkalshetti 2014-07-07T06:42:32Z

Verified in ADS 15.0.0-dev-366

Tested this issue on below distributions for both Export Data and Schema Script Generator:

1] Cloudera Quickstart VM CDH5.0.2 [Hive 0.12] , Distribution: Cloudera, Connection Type: Hiveserver2- Pass

2] Apache Hadoop2.4, Hive 0.13.1, Distribution: Apache, Connection Type: Hiveserver1- Pass
Apache Hadoop2.4, Hive 0.13.1, Distribution: Apache, Connection Type: Hiveserver2 - Pass

3] Hortonworks HDP2.1 Quickstart VM [Hive 0.13] , Distribution: Hortonworks, Connection Type: Hiveserver2 -Pass

4] MapR VM3.1[Hive0.12], Distribution: MapR, Connection Type: Hiveserver2- Pass

Will verify in ADS 14.0.18 patch , when build is available.

anilkalshetti 2014-07-22T09:50:18Z

Verified in ADS 14.0.18
and ADS 15.0.0-alpha-17

anilkalshetti 2014-11-04T09:55:20Z

Verified in ADS 16.0.0-dev-21

Tested this issue in below Hive Distributions

a] Apache Hive 0.13.1, Distribution: Apache, Connection Type: Hiveserver2

Apache Hive 0.13.1, Distribution: Apache, Connection Type: Hiveserver1

b] Hortonworks HDP2.1 Quickstart VM [Hive 0.13] , Distribution: Hortonworks, Connection Type: Hiveserver2

c] Cloudera Quickstart VM CDH5.2 [Hive 0.13.1] , Distribution: Cloudera, Connection Type: Hiveserver2

d] MapR-VM-4.0.1 [Hive 0.12] , Distribution: MapR, Connection Type: Hiveserver2

Search Tips

Aqua Data Studio / nhilam

Schema Script Generator produces INSERT statements which wrong for HIVE

6 attachments

Issue #10855

Completion

Search Tips

Aqua Data Studio / nhilam

Title

Schema Script Generator produces INSERT statements which wrong for HIVE

6 attachments

Issue #10855

Completion