Is format always json when SELECTing from stage?

0
votes

Snowflake supports multiple file types via creation FILE_FORMAT (avro, json, csv etc).

Now I have tested SELECTing from snowflake stage (s3) both:

  • *.avro files (generated from nifi processor batching 10k source oracle table).
  • *.json files (json per line).

And when Select $1 from @myStg, snowflake expands as many rows as records on avro or json files (cool), but.. the $1 variant is both json format and now i wonder if whatever snowflake file_format we use do records always arrive as json on the variant $1 ? I haven't tested csv or others snowflake file_formats.

Or i wonder if i get json from the avros (from oracle table) because maybe NiFi processor creates avro files (with internally uses json format). Maybe im making some confusion here.. i know avro files contain both:

  • avro schema - language similar to json key/value.
  • compressed data (binary).

Thanks, Emanuel O.

1
jsonamazon-s3avrosnowflake-cloud-data-platform
Avro uses a JSON data structure internally with additional protocols on top. As Sriga pointed out for CSV, if the file format isn't JSON internally it will appear different on a select $1. - Greg Pavlik
Thanks @GregPavlik to confirm avro, xml and json gets loaded as $1 single column VARIANT. For CSC, would had been nice to exist a function to JSONize the full CSV line (would be easy to do that - condisering there was header line which would feed json key names). I like consistency, but seems loading CSV is bound to manually load into fixed set of columns.. not possible then to load all CSV line as it is, which is sad.. if for some reason CSV starts bringing more fields one would loose them ince the COPY explicitly loads $1, $2, .. $n fixed number of fields. But for me important is avro.. - Emanuel Oliveira

1 Answers

0
votes

I tried with CSV, When Its came to CSV its parsing each records in the file like belowQuerying stage

So when its came to JSON it will treat one complete JSON as one records so its displaying in JSON format.