CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-s3amazon-athena
ken
ken

Reputation: 668

How can I get data from different S3 buckets by AWS Athena?

I have two AWS S3 buckets. One bucket stores the product user log. Another bucket stores the user attribution(ex. gender, age, etc) data.

I would like to get the user log and attribution data from these buckets and combine the data into one table. May I know how to do that ?

Upvotes: 1

Views: 2335

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32011

just create two tables by using the below method

CREATE [EXTERNAL] TABLE [IF NOT EXISTS]
 [db_name.]table_name [(col_name data_type [COMMENT col_comment] [, ...] )]
 [COMMENT table_comment]
 [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
 [ROW FORMAT row_format]
 [STORED AS file_format] 
 [WITH SERDEPROPERTIES (...)] ]
 [LOCATION 's3://bucket_name/[folder]/']
 [TBLPROPERTIES ( ['has_encrypted_data'='true | false',] ['classification'='aws_glue_classification',] property_name=property_value [, ...] ) ]

then join two tables for creating your desired result table

AWS Docs link

Upvotes: 1

John Rotenstein
John Rotenstein

Reputation: 270224

Quite easy...

  • Use CREATE TABLE to define each table, specifying the LOCATION of the data
  • Run a query that joins both tables, using standard SQL

You can either query the data from both tables simultaneously, or you can create a new table that is the output of such a query by using CREATE TABLE AS.

Upvotes: 3

Related Questions