Create DynamoDB tables using Hive

Question

I have in my cloud, inside a S3 bucket, a CSV file with some data.

I would like to export that data into a DynamoDB table with columns "key" and "value".

Here's the current hive script I wrote:

CREATE EXTERNAL TABLE FromCSV(key string, value string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ', '
LOCATION 's3://mybucket/output/';                    

CREATE EXTERNAL TABLE hiveTransfer(col1 string, col2 string)
STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler' 
TBLPROPERTIES ("dynamodb.table.name" = "InvertedIndex", 
"dynamodb.column.mapping" = "col1:key,col2:value");  

INSERT OVERWRITE TABLE hiveTransfer SELECT * FROM FromCSV;

Now, basically the script works. though I would like to make some modifications to this script as follows:

1) The script works only if the table "InvertedIndex" already exists in DynamoDB, I would like the script to create the new table by itself and then put the data as it already does.

2) In the CSV the key is always a string but I have 2 kinds of values, string or integer. I would like the script to distinguish between the two and make two different tables.

Any help with those two modifications will be appriciated. Thank you

dino.keco · Accepted Answer · 2013-06-27 07:47:28Z

3

Hi this could be accomplished but it is not trivial case.

1) For creating dynamo table that can't be done by hive because Dynamo Tables are managed by Amazon cloud. One thing which gets in my mind is to create Hive UDF for creating dynamo table and call it inside some dummy query before running insert. For example:

 SELECT CREATE_DYNO_TABLE() FROM dummy;

Where dummy table has only one record.

2) You can split loading into two queries where in one query you will use RLIKE operator and [0-9]+ regular expression to detect numeric values and other just negation of that.

HTH, Dino

answered Jun 27, 2013 at 7:47

dino.keco

1,4011 gold badge12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DanielY Over a year ago

Dino, thank you very much :)! Can you just please write me an example of a query of the kind I need in number 2? (maybe based on my script)...

dino.keco Over a year ago

SELECT * from FromCSV WHERE value RLIKE '[0-9]+'

Collectives™ on Stack Overflow

Create DynamoDB tables using Hive

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related