CODEWITHSUNDEEP
amazon-web-servicesprestoamazon-athena
Bostonian
Bostonian

Reputation: 687

create bucket table in AWS Athena

I tried below query to create a bucket table but failed. However, if I remove the clause CLUSTERED BY, the query could succeed. Any suggestion? Thank you.

the error message: no viable alternative at input create external

CREATE EXTERNAL TABLE nation5(
  n_nationkey bigint, 
  n_name string, 
  n_rgionkey int, 
  n_comment string)
CLUSTERED BY 
  n_regionkey INTO 256 BUCKETS
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
WITH SERDEPROPERTIES ( 
  'path'='s3://test/testbucket/nation5/') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://test/testbucket/nation5/'

Upvotes: 0

Views: 1420

Answers (1)

Philipp Johannis
Philipp Johannis

Reputation: 2956

The CLUSTERED BY column needs to be in brackets, the following works:

CREATE EXTERNAL TABLE test.nation5(
  n_nationkey bigint, 
  n_name string, 
  n_regionkey int, 
  n_comment string)
CLUSTERED BY 
  (n_regionkey) INTO 256 BUCKETS
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
WITH SERDEPROPERTIES ( 
  'path'='s3://test/testbucket/nation5/') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://test/testbucket/nation5/'

(You also have a spelling mistake in n_rgionkey in the column definition.

Upvotes: 2

Related Questions