CODEWITHSUNDEEP
pythonjsonsnowflake-cloud-data-platform
rocket4444
rocket4444

Reputation: 1

Can you use Python to write Raw Json to Snowflake Stage

I am trying to use Python to write raw Json to Snowflake Stage. I've used Snowflake's PUT file://file.json.gz @stage but that implies I need to write the file temporary to disk or cloud storage before storing it on a stage. This process works, however, the data I am sending is not allowed to be stored in the Cloud.
Is there a way I can write files to the stage directly? Or is this not possible with Snowflake at the moment? Thank you

Upvotes: -1

Views: 978

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11046

You can write JSON directly to a Snowflake stage using the COPY command and PARSE_JSON function.

create or replace stage MY_JSON_STAGE;

copy into @MY_JSON_STAGE/MY_FILE_NAME.json from 
(select parse_json(
$$
{
    "glossary": {
        "title": "example glossary",
        "GlossDiv": {
            "title": "S",
            "GlossList": {
                "GlossEntry": {
                    "ID": "SGML",
                    "SortAs": "SGML",
                    "GlossTerm": "Standard Generalized Markup Language",
                    "Acronym": "SGML",
                    "Abbrev": "ISO 8879:1986",
                    "GlossDef": {
                        "para": "A meta-markup language, used to create markup languages such as DocBook.",
                        "GlossSeeAlso": ["GML", "XML"]
                    },
                    "GlossSee": "markup"
                }
            }
        }
    }
}
$$)) file_format = (type = json);

With a bit of fancy footwork, it's possible to write any string to a Snowflake stage directly using only Snowflake SQL. In this example, the JSON is stringified. Because it's possible to do this using SQL, you can do this from a script, stored procedure, or any client including Python.

create or replace file format RAW_STRING
    compression = 'auto'
    field_delimiter = 'NONE'
    record_delimiter = 'NONE'
    skip_header = 0
    field_optionally_enclosed_by = 'NONE'
    trim_space = FALSE
    error_on_column_count_mismatch = FALSE
    escape = 'NONE'
    escape_unenclosed_field = 'NONE'
;
create or replace stage RAW_STRING;

copy into @RAW_STRING/MY_FILE_NAME.txt from 
(select
$$
{
    "glossary": {
        "title": "example glossary",
        "GlossDiv": {
            "title": "S",
            "GlossList": {
                "GlossEntry": {
                    "ID": "SGML",
                    "SortAs": "SGML",
                    "GlossTerm": "Standard Generalized Markup Language",
                    "Acronym": "SGML",
                    "Abbrev": "ISO 8879:1986",
                    "GlossDef": {
                        "para": "A meta-markup language, used to create markup languages such as DocBook.",
                        "GlossSeeAlso": ["GML", "XML"]
                    },
                    "GlossSee": "markup"
                }
            }
        }
    }
}
$$) file_format = (format_name = 'RAW_STRING');

Upvotes: 3

Related Questions