saving athena results to another table with partitions-CodePudding

From AWS Athena

I am trying to concatenate multiple tables then save it with partitoned key.

after running

select *
  from t1
union all
select *
  from t2
select *
  from t3

create table on the console creates query like this,

create table db.table_name
with(
format='parquet',
external_location=...
) AS
select *
  from t1
union all
select *
  from t2
select *
  from t3;

But I want to add partitoned by column. I've tried

adding partitioned by on top and bottom. Also saved query result then created new table from that using CREATE EXTERNAL TABLE command (this works but return empty row -> even after running MSCK REPAIR)

From https://aws.amazon.com/premiumsupport/knowledge-center/athena-create-use-partitioned-tables/ It seems like I need to save data into S3 by partitions so in bucket1 it will have bucket1/2021, bucket1/2022 if 'year' column is the partition column. Correct? If yes, is there efficient to create partitioned buckets?

CodePudding user response：

I have successfully created new, partitioned tables by using this method:

CREATE TABLE my_table
WITH (
 format = 'PARQUET',
 parquet_compression = 'SNAPPY',
 external_location = 's3://bucket/folder/',
 partitioned_by = ARRAY['year']
)
AS
SELECT
  ...