14

Athena creates a temporary table using fields in S3 table. I have done this using JSON data. Could you help me on how to create table using parquet data?

I have tried following:

  1. Converted sample JSON data to parquet data.
  2. Uploaded parquet data to S3.
  3. Created temporary table using columns of JSON data.

By doing this I am able to a execute query but the result is empty.

Is this approach right or is there any other approach to be followed on parquet data?

Sample json data:

{"_id":"0899f824e118d390f57bc2f279bd38fe","_rev":"1-81cc25723e02f50cb6fef7ce0b0f4f38","deviceId":"BELT001","timestamp":"2016-12-21T13:04:10:066Z","orgid":"fedex","locationId":"LID001","UserId":"UID001","SuperviceId":"SID001"},
{"_id":"0899f824e118d390f57bc2f279bd38fe","_rev":"1-81cc25723e02f50cb6fef7ce0b0f4f38","deviceId":"BELT001","timestamp":"2016-12-21T13:04:10:066Z","orgid":"fedex","locationId":"LID001","UserId":"UID001","SuperviceId":"SID001"}
John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
rajeswari
  • 279
  • 1
  • 4
  • 13
  • Would you please share your Athena table definition? What tool did you use to generate Parquet files? – James Mar 14 '17 at 16:04
  • If you perform a `SELECT * FROM foo LIMIT 5`, does it return any data? If not, then your table definition is not valid. – John Rotenstein Mar 14 '17 at 20:50

3 Answers3

20

If your data has been successfully stored in Parquet format, you would then create a table definition that references those files.

Here is an example statement that uses Parquet files:

CREATE EXTERNAL TABLE IF NOT EXISTS elb_logs_pq (
  request_timestamp string,
  elb_name string,
  request_ip string,
  request_port int,
  ...
  ssl_protocol string )
PARTITIONED BY(year int, month int, day int) 
STORED AS PARQUET
LOCATION 's3://athena-examples/elb/parquet/'
tblproperties ("parquet.compress"="SNAPPY");

This example was taken from the AWS blog post Analyzing Data in S3 using Amazon Athena that does an excellent job of explaining the benefits of using compressed and partitioned data in Amazon Athena.

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
  • Can you exclude partitions? For example only include day 1, 3, 5 and exclude all other days. – user1141785 Aug 13 '20 at 15:34
  • I assume the table data is updated when the data on s3 changes? That means A) when there is a deletion rule on s3 the data is deleted at the table B) when new days are added on S3 then the data is added to the table – user1141785 Aug 13 '20 at 15:38
2

If your table definition is valid but not getting any rows, try this

-- The MSCK REPAIR TABLE command will load all partitions into the table. -- This command can take a while to run depending on the number of partitions to be loaded.

MSCK REPAIR TABLE {tablename}

Eric Linden
  • 21
  • 2
1

steps:
1. create your my_table_json
2. insert data into my_table_json (verify existence of the created json files in the table 'LOCATION')
3. create my_table_parquet: same create statement as my_table_json except you need to add 'STORED AS PARQUET' clause.
4. run: INSERT INTO my_table_parquet SELECT * FROM my_table_json

belostoky
  • 934
  • 2
  • 11
  • 22