Creating External Table from partitioned parquet table

data_extractor
New Contributor II

‎03-20-2024 08:42 AM

I am trying to create an external table in catalog using parquet where the parquet file is partitioned

I have tried using the below syntax,

%sql

CREATE TABLE table_name

(

col1 type1, 

col2 type2, 

col3 type3, 

)

USING parquet

 PARTITIONED BY ( col4 type4)

LOCATION "s3://<bucket-name>/<folder-name>/parquet_file/"

 

which gives success in creating the table

but no data is shown when i try to select the table

 

Can someone let me know what am i doing wrong.

0 Kudos

MichTalebzadeh
Valued Contributor

‎03-20-2024 01:51 PM

This is my guess


Your code (LOCATION "s3://<bucket-name>/<folder-name>/parquet_file/) is likely pointing to a single Parquet file instead of the entire folder containing partitioned data.
try this

CREATE TABLE table_name
(
  col1 type1,
  col2 type2,
  col3 type3,
)
USING parquet
PARTITIONED BY (col4 type4)
LOCATION "s3://<bucket-name>/<folder-name>/"  -- Include trailing slash for the folder
Mich Talebzadeh | Technologist | Data | Generative AI | Financial Fraud
London
United Kingdom

view my Linkedin profile



https://en.everybodywiki.com/Mich_Talebzadeh



Disclaimer: The information provided is correct to the best of my knowledge but of course cannot be guaranteed . It is essential to note that, as with any advice, quote "one test result is worth one-thousand expert opinions (Werner Von Braun)".
0 Kudos

Chizzy
New Contributor II

‎09-24-2024 02:47 AM

@data_extractor , How were you able to fix this problem? I am having same issue now

0 Kudos