title: Export Dynamodb to S3 using Hive
body: <p>I referred to this link: <a href="http://docs.aws.amazon.com/emr/latest/ReleaseGuide/EMR_Hive_Commands.html" rel="nofollow noreferrer">http://docs.aws.amazon.com/emr/latest/ReleaseGuide/EMR_Hive_Commands.html</a>.</p>
  
  <p>My hive script is like below:</p>
  
  <pre><code>DROP TABLE IF EXISTS hiveTableName;
  CREATE EXTERNAL TABLE hiveTableName (item map&lt;string,string&gt;)
  STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler' 
  TBLPROPERTIES ("dynamodb.table.name" = "test_table", "dynamodb.region"="us-west-2");  
  
  DROP TABLE IF EXISTS s3TableName;
  CREATE EXTERNAL TABLE s3TableName (item map&lt;string, string&gt;)
  ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
  LOCATION 's3://bucket/test-hive2'; 
  
  SET dynamodb.throughput.read.percent=0.8;   
  
  INSERT OVERWRITE TABLE s3TableName SELECT * 
  FROM hiveTableName; 
  </code></pre>
  
  <p>Dynamodb table can be successfully exported to S3, but the file format is not JSON, it is like:</p>
  
  <pre><code>uuid{"s":"db154955-8555-4b49-bf40-ee36605ac510"}num{"n":"1294"}info{"s":"qwefjdkslafjdafl"}
  uuid{"s":"d9898564-2b56-42ba-9cfb-fd092e7d0b8d"}num{"n":"100"}info{"s":"qwefjdkslafjdafl"}
  </code></pre>
  
  <p>Does someone know how to export in JSON format? I know I can use Data Pipeline, and it can export dynamodb table to S3 in JSON format, but for some reason I need to use EMR. I tried another tool: <a href="https://github.com/awslabs/emr-dynamodb-connector" rel="nofollow noreferrer">https://github.com/awslabs/emr-dynamodb-connector</a>, and use the command:</p>
  
  <pre><code>java -cp target/emr-dynamodb-tools-4.2.0-SNAPSHOT.jar org.apache.hadoop.dynamodb.tools.DynamoDBExport /where/output/should/go my-dynamo-table-name
  </code></pre>
  
  <p>but the error was </p>
  
  <pre><code>Error: Could not find or load main class org.apache.hadoop.dynamodb.tools.DynamoDBExport
  </code></pre>
  
  <p>Can someone tell me how to solve these problems? Thanks.</p>
  
  <p>== update ==</p>
  
  <p>If I use to_json, as Chris suggested, my code is as below:</p>
  
  <pre><code>DROP TABLE IF EXISTS hiveTableName2;
  CREATE EXTERNAL TABLE hiveTableName2 (item map&lt;string, string&gt;)
  STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler' 
  TBLPROPERTIES ("dynamodb.table.name" = "test_table", "dynamodb.region"="us-west-2");  
  
  DROP TABLE IF EXISTS s3TableName2;
  CREATE EXTERNAL TABLE s3TableName2 (item string)
  ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
  LOCATION 's3://backup-restore-dynamodb/hive-test'; 
  
  INSERT OVERWRITE TABLE s3TableName2 SELECT to_json(item)
  FROM hiveTableName2; 
  </code></pre>
  
  <p>When I look at the generated file, it's like</p>
  
  <pre><code>{"uuid":"{\"s\":\"db154955-8555-4b49-bf40-ee36605ac510\"}","num":"{\"n\":\"1294\"}","info":"{\"s\":\"qwefjdkslafjdafl\"}"}
  </code></pre>
  
  <p>What I want is a nested map, like </p>
  
  <pre><code>map&lt;string, map&lt;string, string&gt;&gt;
  </code></pre>
  
  <p>not </p>
  
  <pre><code>map&lt;string, string&gt;
  </code></pre>
  
  <p>Can someone give me some suggestions? Thanks.</p>