I have a centralized schema with internal stage for landing files. I want to separate the files in groups( finance, customer etc...) using subfolders under the same internal stage. For some reason I can't read files in the subfolder but can at the root of the stage
1st works and returns the structure but the 2nd statement returns empty []. I have verified that files are there using LIST.
SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
FROM TABLE(
INFER_SCHEMA(
LOCATION=>'@dev_internal_utility_file_stage/CLAIM_SM--760b84c0-29d9-36b9-bda3-5af53486cfe2_34cea2a0-9f10-42d7-aa50-f309f19fabe6.parquet',
-- FILES=>'.sdc--CLAIM_SM--.*.parquet',
FILE_FORMAT=>'DEV.UTILITY.DEV_INTERNAL_PARQUET_FILE_FORMAT'
)
) ;
SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
FROM TABLE(
INFER_SCHEMA(
LOCATION=>'@dev_internal_utility_file_stage/secure_finance/CLAIM_SM--760b84c0-29d9-36b9-bda3-5af53486cfe2_34cea2a0-9f10-42d7-aa50-f309f19fabe6.parquet',
--FILES=>'.*sdc--CLAIM_SM--*.parquet',
FILE_FORMAT=>'DEV_PSA.UTILITY.DEV_INTERNAL_PARQUET_FILE_FORMAT'
)
) ;
Any ideas?