Context
I have a stage where I have two files:
SCH_DTM_TEST/TEST/microsoft_graph_api_user_full_q1_file_1_20240221_110417.json.gz
SCH_DTM_TEST/TEST/microsoft_graph_api_manager_full_q1_file_1_20240221_110627.json.gz
I loaded them there through a put command.
I have those queries:
COPY INTO sch_stg_support.test_user FROM ( SELECT METADATA$FILENAME,METADATA$FILE_ROW_NUMBER,METADATA$FILE_LAST_MODIFIED::TIMESTAMP_LTZ,$1 FROM '@~/SCH_DTM_TEST/TEST/') PATTERN= '.*user.*' FILE_FORMAT = (TYPE=JSON STRIP_OUTER_ARRAY= TRUE);
COPY INTO sch_stg_support.test_manager FROM ( SELECT METADATA$FILENAME,METADATA$FILE_ROW_NUMBER,METADATA$FILE_LAST_MODIFIED::TIMESTAMP_LTZ,$1 FROM '@~/SCH_DTM_TEST/TEST/') PATTERN= '.*manager.*' FILE_FORMAT = (TYPE=JSON STRIP_OUTER_ARRAY= TRUE);
I want to copy into one of the table some metadata and the content of a json file which have user in it, and to the other table the file with manager.
Issue
I have is that the pattern seems to misbehave. No issue in the copy into in itself, but the first query takes the user AND the manager file, while the second query only take the manager file as intended.
I tried to move the PATTERN inside the select :
COPY INTO sch_stg_support.test FROM ( SELECT METADATA$FILENAME,METADATA$FILE_ROW_NUMBER,METADATA$FILE_LAST_MODIFIED::TIMESTAMP_LTZ,$1 FROM '@~/SCH_DTM_TEST/TEST/' (PATTERN => '.*user.*')) FILE_FORMAT = (TYPE=JSON STRIP_OUTER_ARRAY= TRUE)
but I still have the same issue.
I added folders in my stage to better segregate the files, but I wanted to know if there was something I was doing wrong in my query.