Querying Metadata Columns

For each data file staged to an internal stage or staging location, Snowflake can generate additional columns which contain metadata about the data being copied. The columns are only available within a query, but they can be loaded into a table along with the data columns using COPY INTO table.

Currently, the following metadata columns can be queried or copied into tables:

METADATA$FILENAME
Name of the staged data file the current row belongs to.
METADATA$FILE_ROW_NUMBER
Row number for each record in the container staged data file.

In this Topic:

Querying Metadata Columns in Staged Files

You can query metadata columns just as you would other columns.

Example

The following example stages a CSV data file and queries the metadata and regular data columns in the file:

-- Create an internal stage
CREATE OR REPLACE STAGE mystage;

-- Stage a data file
PUT file:///tmp/contacts.csv @mystage;

-- Query the filename and row number metadata columns and the regular data columns in the staged file:
SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, t.$1, t.$2, t.$3, t.$4 FROM @mystage/contacts.csv.gz t;

+-------------------+--------------------------+---------+---------+-----------------------------------+----------------+
| METADATA$FILENAME | METADATA$FILE_ROW_NUMBER | $1      | $2      | $3                                | $4             |
|-------------------+--------------------------+---------+---------+-----------------------------------+----------------|
| contacts.csv.gz   |                        1 | Burnett | Ishmael | vitae.erat@necmollisvitae.ca      | 1-872-600-7301 |
| contacts.csv.gz   |                        2 | Fields  | Ian     | rutrum.non@condimentumDonec.co.uk | 1-138-621-8354 |
| contacts.csv.gz   |                        3 | Jones   | Acosta  | Nunc.lectus@a.org                 | 1-834-909-8838 |
| contacts.csv.gz   |                        4 | Tacitus | Sophia  | lectus.pede@non.ca                | 1-962-503-3253 |
| contacts.csv.gz   |                        5 | Watson  | Dorothy | diam.dictum@fermentum.co.uk       | 1-158-596-8622 |
+-------------------+--------------------------+---------+---------+-----------------------------------+----------------+

Loading Metadata Columns into a Table

The COPY INTO table command can copy metadata from staged data files into a target table.

Example

The following example loads the metadata columns and regular data columns into a table:

CREATE OR REPLACE TABLE mytable (
  filename STRING,
  file_row_number STRING,
  last_name STRING,
  first_name STRING,
  email STRING,
  cellphone STRING
  );

COPY INTO mytable(filename, file_row_number, last_name, first_name, email, cellphone)
     FROM (SELECT METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, t.$1, t.$2, t.$3, t.$4 FROM @mystage/contacts.csv.gz t);

SELECT * FROM mytable;

+-----------------+-----------------+-----------+------------+-----------------------------------+----------------+
| FILENAME        | FILE_ROW_NUMBER | LAST_NAME | FIRST_NAME | EMAIL                             | CELLPHONE      |
|-----------------+-----------------+-----------+------------+-----------------------------------+----------------|
| contacts.csv.gz | 1               | Burnett   | Ishmael    | vitae.erat@necmollisvitae.ca      | 1-872-600-7301 |
| contacts.csv.gz | 2               | Fields    | Ian        | rutrum.non@condimentumDonec.co.uk | 1-138-621-8354 |
| contacts.csv.gz | 3               | Jones     | Acosta     | Nunc.lectus@a.org                 | 1-834-909-8838 |
| contacts.csv.gz | 4               | Tacitus   | Sophia     | lectus.pede@non.ca                | 1-962-503-3253 |
| contacts.csv.gz | 5               | Watson    | Dorothy    | diam.dictum@fermentum.co.uk       | 1-158-596-8622 |
+-----------------+-----------------+-----------+------------+-----------------------------------+----------------+

Limitations

  • Metadata columns are read-only. You cannot insert metadata into existing table rows.

  • Metadata columns can only be queried by name; as such, they are not included in the output of any of the following queries: