I am trying to create the table below:
create or replace table great_table as (
SELECT
$1:test::STRING as testt,
$1:testt::STRING as account_name,
$1:testttt::STRING as testttt,
$1:testttttt::DATE as testttttt
from '@A_STAGE/20210510/object_name/part'
)
;
But I want to get all data from 2021, and not have to reconfigure the bucket structure. Is there a way to do regex from snowflake within the stage name? So something like
@A_STAGE/202%/object_name/part'
I believe what you are looking for is to leverage the PATTERN option to query from a stage. The documentation for this option is located here, and what you'd end up doing is selecting from @A_STAGE and then using PATTERN in your format options to define the regex of files (and folders) that you want to select from.
https://docs.snowflake.com/en/user-guide/querying-stage.html#query-syntax-and-parameters
However, if you are creating a table from that select, you probably should either be running a COPY INTO statement, which also the same PATTERN option:
https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html
Or, maybe you could look into leveraging an external table, instead, which can have advantages depending on how the underlying files in S3 are being created, replaced, removed, etc.
https://docs.snowflake.com/en/sql-reference/sql/create-external-table.html