使用AWS Athena在AWS胶中复制表

Question

null

这些服务都是为了协同工作而设计的，因此必须有一个适当的方法来完成这一点。任何建议都将不胜感激：）

Answer 1

这样做的方法是使用CTAS查询语句。

create TABLE AS SELECT(CTAS)查询根据另一个查询的SELECT语句的结果在Athena中创建一个新表。Athena将CTAS语句创建的数据文件存储在Amazon S3中的指定位置。

例如：

CREATE TABLE new_table
WITH (
     external_location = 's3://my_athena_results/new_table_files/'
) AS (
    -- Here goes your normal query 
    SELECT 
        *
    FROM 
        old_table;
)

1. The destination location for storing CTAS query results in Amazon S3 must be empty.
2. The same applies to the name of new table, i.e. it shouldn't exist in AWS Glue Data Catalog.
3. In general, you don't have explicit control of how many files will be created as a result of CTAS query, since Athena is a distributed system. However, can try this to use "this workaround" which uses bucketed_by and bucket_count fields within WITH clause

   CREATE TABLE new_table
   WITH (
       external_location = 's3://my_athena_results/new_table_files/',
       bucketed_by=ARRAY['some_column_from_select'],
       bucket_count=1
   ) AS (
       -- Here goes your normal query 
       SELECT 
           *
       FROM 
           old_table;
   )