folder with multiple files

[zhenriquegomes]

I have a folder in S3 's3://prod-raw-cvm/dados_abertos/2024-12-10/adm_cart/cad/dados/cad_adm_cart.zip/'. But, in this folder I have multiple files. So, I tried to create one table for each file, like this:
`
CREATE TABLE IF NOT EXISTS hive.cvm.cad_adm_cart_pf (
ADMIN VARCHAR,
DT_REG VARCHAR,
DT_CANCEL VARCHAR,
MOTIVO_CANCEL VARCHAR,
SIT VARCHAR,
DT_INI_SIT VARCHAR,
CATEG_REG VARCHAR
)
WITH (
external_location = 's3://prod-raw-cvm/dados_abertos/2024-12-10/adm_cart/cad/dados/cad_adm_cart.zip/cad_adm_cart_pf.csv',
format = 'CSV',
csv_separator = ';',
skip_header_line_count = 1
);

CREATE TABLE IF NOT EXISTS hive.cvm.cad_adm_cart_pj (
ADMIN VARCHAR,
DT_REG VARCHAR,
DT_CANCEL VARCHAR,
MOTIVO_CANCEL VARCHAR,
SIT VARCHAR,
DT_INI_SIT VARCHAR,
CATEG_REG VARCHAR
)
WITH (
external_location = 's3://prod-raw-cvm/dados_abertos/2024-12-10/adm_cart/cad/dados/cad_adm_cart.zip/cad_adm_cart_pj.csv',
format = 'CSV',
csv_separator = ';',
skip_header_line_count = 1
);
`

And this does not work. It only works loading all the files from the source. I'm using hive with this configs:
connector.name=hive
hive.metastore=glue

hive.metastore.glue.aws-access-key=MY_KEY
hive.metastore.glue.aws-secret-key=MY_SECRET
hive.metastore.glue.region=sa-east-1

fs.native-s3.enabled=true
s3.path-style-access=true
s3.region=sa-east-1
s3.endpoint=https://s3.sa-east-1.amazonaws.com/
s3.aws-access-key=MY_KEY
s3.aws-secret-key=MY_SECRET

[hashhar]

Hive tables can only point to folders not to individual files. This is how hive itself works.

Alternatively look at trino-storage plugin (https://github.com/snowlift/trino-storage).

[zhenriquegomes]

Thank you!