hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mich Talebzadeh <mich.talebza...@gmail.com>
Subject Inserting into a Hive transactional table using Java code
Date Mon, 14 Mar 2016 23:31:25 GMT
Hive 2:00
Spark engine 1.3.1
Eclipse Scala IDE build of Eclipse SDK

A simple routine that reads CSV files from a staging directory by creating
an external table and insert into an ORC transactional table.

Using beeline from another server the task finishes pretty quickly. The
code is shown in attachment 1 (import_csv.hql).

However, when I use Eclipse to run the code as a Java app, The insert into
table hangs trying to insert 65 rows. Interestingly it only holds shared
read lock on the target table

Lock ID Database        Table   Partition       State   Type    Transaction
ID  Last Hearbeat   Acquired At     User    Hostname
2000    test    t2      NULL    ACQUIRED        *SHARED_READ*     NULL
1457998918685   1457998618675   hduser  rhes564

This is the progress of the code

14/03/2016 23:36:19.19
 Running: CREATE EXTERNAL TABLE stg_t2 ( INVOICENUMBER string ,PAYMENTDATE
string ,NET string ,VAT string ,TOTAL string )COMMENT 'from csv file from
excel sheet PayInsPeridaleTechnology' ROW FORMAT serde
'org.apache.hadoop.hive.serde2.OpenCSVSerde' STORED AS TEXTFILE LOCATION
'/data/stg/table2' TBLPROPERTIES ('skip.header.line.count'='1')

 Running: CREATE TABLE t2 ( INVOICENUMBER          INT
,PAYMENTDATE            timestamp,NET
DECIMAL(20,2),VAT                    DECIMAL(20,2),TOTAL
DECIMAL(20,2))COMMENT 'from csv file from excel sheet
PayInsPeridaleTechnology' CLUSTERED BY (INVOICENUMBER) INTO 256 BUCKETS
STORED AS ORC TBLPROPERTIES ( 'orc.compress'='ZLIB', 'transactional'='true')

Hanging here

 Running: INSERT INTO TABLE t2 SELECT INVOICENUMBER ,
CAST(UNIX_TIMESTAMP(paymentdate,'DD/MM/YYYY')*1000 as timestamp) ,
CAST(REGEXP_REPLACE(net,'[^\d\.]','') AS DECIMAL(20,2)) ,
CAST(REGEXP_REPLACE(vat,'[^\d\.]','') AS DECIMAL(20,2)) ,
CAST(REGEXP_REPLACE(total,'[^\d\.]','') AS DECIMAL(20,2)) FROM stg_t2 WHERE
CAST(REGEXP_REPLACE(total,'[^\d\.]','') AS DECIMAL(20,2)) > 0.0



Dr Mich Talebzadeh



LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*



http://talebzadehmich.wordpress.com

Mime
View raw message