CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformdbt
user3735871
user3735871

Reputation: 317

How to skip a row in Snowflake select statement

I'm trying to run the query below to load a table in Snowflake via DBT. The data source is a csv file with a header. How I can skip the header in a statement like this? I understand that there's an option to use Copy Into, but I hope to use this query format. Thanks.

Select t.$1 as col1, t.$2 as col2
from @stage (pattern =>'.*/path/to/filename.csv') t //how do I skip the first row in filename.csv?

Upvotes: 0

Views: 1833

Answers (2)

Adam Kipnis
Adam Kipnis

Reputation: 11001

Snowflake has metadata info available to you when reading from a stage for things like file name, row number, file timestamp, etc. You can use that to skip the first row:

Select t.$1 as col1, t.$2 as col2
from @stage (pattern =>'.*/path/to/filename.csv') t
where metadata$file_row_number > 1;

Full list of metadata fields can be found here.

Upvotes: 1

sprethepa
sprethepa

Reputation: 797

You can use the file_format option in the staged query.

File_format has an option SKIP_HEADER. You can use SKIP_HEADER column available in file_format for your staged query to skip the header.

https://docs.snowflake.com/en/user-guide/querying-stage

https://docs.snowflake.com/en/sql-reference/sql/create-file-format?utm_source=snowscope&utm_medium=serp&utm_term=file+format

Upvotes: 1

Related Questions