COPY INTO with partitioned ADLS

I do not think that you have anything to do with the pattern parameter.

You said you do not have a hive partition column that matches the pattern of the directories. If you do not have a column to use these partitions, then they are probably not beneficial for querying the data. Maybe they were generated to help maintenance. If this is the case, ignore the partition, and read all files with the COPY command.

If you think having such a column would help, then the blog post (you mentioned) already shows how you can parse the filenames to generate the column value. Add the partition column to your table (and even you may define it as the clustering key), and run the COPY command to read all files in all partitions/directories, parse the value of the column from the file name.

For parsing the partition value, I would use this one which seems easier:

copy into TARGET_TABLE from (
   select 
       REGEXP_SUBSTR (
       METADATA$FILENAME,
       '.*\ABC_PARTITIONED_ID=(.*)\/.*', 
       1,1,'e',1
      ) partitioned_column_value,
      $1:column_name,
   ...
   from @your_stage/data_folder/);

If the directory/partition name doesn't matter to you, then you can use some of the newer functions in Public Preview that support Parquet format to create the table and ingest the data. Your question on how to construct the pattern would be PATTERN='*.parquet' as all subfolders would be read.

    //create file format , only required to create one time
    create file format my_parquet_format
      type = parquet;
     
    //EXAMPLE CREATE AND COPY INTO FOR TABLE1
    //create an empty table using this file format and location. name the table table1
     create or replace table ABC
      using template (
        select array_agg(object_construct(*))
          from table(
            infer_schema(
              location=>'@mystage/ABC_PARTITIONED_ROOT',
              file_format=>'my_parquet_format'
            )
          ));
         //copy parquet files in folder /table1 into table TABLE1
        copy into ABC from @mystage/ABC_PARTITIONED_ROOT pattern = '*.parquet' file_format=my_parquet_format match_by_column_name=case_insensitive;

This should be possible by creating a storage integration, granting access in Azure for Snowflake to access the storage location, and then creating an external stage.

Alternatively you can generate a shared access signature (SAS) token to grant Snowflake (limited) access to objects in your storage account. You can then access an external (Azure) stage that references the container using the SAS token.

Snowflake metadata provides

• METADATA$FILENAME - Name of the staged data file the current row belongs to. Includes the path to the data file in the stage.
• METADATA$FILE_ROW_NUMBER - Row number for each record

We could do something like this:

    select $1:normal_column_1, ..., METADATA$FILENAME  
        FROM
            '@stage_name/path/to/data/' (pattern => '.*.parquet')
    limit 5;

For example: it would give something like:

METADATA$FILENAME 
----------
path/to/data/year=2021/part-00020-6379b638-3f7e-461e-a77b-cfbcad6fc858.c000.snappy.parquet

we need to handle deducing the column from it. We could do a regexp_replace and get the partition value as column like this:

    select 
        regexp_replace(METADATA$FILENAME, '.*\/year=(.*)\/.*', '\\1'
        ) as year
        $1:normal_column_1,  
    FROM
            '@stage_name/path/to/data/' (pattern => '.*.parquet')
    limit 5;

• In the above regexp, we give the partition key.
• Third parameter \\1 is the regex group match number. In our case, first group match - this holds the partition value.

More detailed answer and other approaches to solve this issue is available on this stackoverflow answer