CODEWITHSUNDEEP
pythonsnowflake-cloud-data-platform
SDM
SDM

Reputation: 11

Storing Sql Queries in Json file

I am messing around with some python and snowflake. I am using a JSON file to store my connection info like this:

{
  "snowflake_config": {
    "user":      "automation",
    "password":  "************",
    "account":   "someaccounthere",
    "warehouse": "somewarehousehere",
    "database":  "somedatabasehere",
    "schema":    "someschemahere"
  }
}

But I have a few hundred SQL queries that I would like to run that will accept arguments. Can I store them in a json file as well? Or does snowflake using execute_stream really require the file to be static? I was hoping I could do something like this (pseudo code):

{
 "snowflake_queries": {
  "sql1": "GRANT SELECT ON ALL TABLES IN SCHEMA %s<this is argparse1> to role <argparse2>",
  "sql2": "GRANT SELECT ON ALL VIEWS IN SCHEMA %s<this is argparse1> to role <argparse2>"
 }
}

Upvotes: 0

Views: 2892

Answers (1)

Hans Henrik Eriksen
Hans Henrik Eriksen

Reputation: 2870

I don't think execute_stream() can handle parameters, but why do you want to use it? execute_stream() is for verbatim execution of a script like Run All Queries in a Snowflake Web GUI worksheet. That means you can use eg. SQL variables as parameters in the script, but you can't use parameters from somewhere else unless you prepend eg. SET myvar = value; to the script.

I suggest you use just execute() or perhaps executemany(), which both take parameters.
In general, they work like this with a tuple of parameters:

snowflake_queries = {
  "q1": "GRANT SELECT ON ALL TABLES IN SCHEMA IDENTIFIER(:1) to role IDENTIFIER(:2)",
  "q2": "GRANT SELECT ON ALL VIEWS IN SCHEMA IDENTIFIER(:1) to role IDENTIFIER(:2)"
}

for key, sqlText in snowflake_queries.items():
  my_cursor.execute(sqlText, ((SomeSchema, SomeRole)))

There are some commands and functions that have parts that cannot be substituted by parameters yet. Those are (Dec 2019) import/export command parameters and notably time travel eg. AT(OFFSET => n) and the GENERATOR(ROWCOUNT => n) function.
Then you have to do string formatting yourself in the sqlText.

In other commands it is possible to use non-literal parameters with the IDENTIFIER() wrapper, eg. schema/table and user/role names. I've used this in the code above.

A final word:
The case above (an administrative helper script) is a perfect candidate for a Stored Procedure.

Upvotes: 2

Related Questions