def test_special_schema_character(db_parameters):
"""Make sure we decode special characters correctly"""
# Constants
database = "a/b/c" # "'/'.join([choice(ascii_lowercase) for _ in range(3)])
schema = "d/e/f" # '/'.join([choice(ascii_lowercase) for _ in range(3)])
# Setup
options = dict(**db_parameters)
conn = connect(**options)
conn.cursor().execute(
"CREATE OR REPLACE DATABASE \"{0}\"".format(database))
conn.cursor().execute("CREATE OR REPLACE SCHEMA \"{0}\"".format(schema))
conn.close()
# Test
options.update({
'database': '"' + database + '"',
'schema': '"' + schema + '"'
})
sf_conn = connect(**options)
sf_connection = [
res for res in sf_conn.cursor().execute("select current_database(), "
"current_schema();")
]
sa_conn = create_engine(URL(**options)).connect()
sa_connection = [
res for res in sa_conn.execute("select current_database(), "
"current_schema();")
]
sa_conn.close()
sf_conn.close()
# Teardown
conn = connect(**options)
conn.cursor().execute("DROP DATABASE IF EXISTS \"{0}\"".format(database))
conn.close()
assert [(database, schema)] == sf_connection == sa_connection
def _setup(self, query, host, user, password, account, warehouse, database, schema, protocol='https', port=443):
con = connector.connect(
host=host,
user=user,
password=password,
account=account,
warehouse=warehouse,
database=database,
schema=schema,
protocol='https',
port=port)
# Create a cursor object.
cur = con.cursor()
cur.execute(query)
df = cur.fetch_pandas_all()
cur.close()
con.close()
self._database = database
self._warehouse = warehouse
col_map = {}
for col in df.columns:
col_map[col] = col
return df, col_map
def get_conn(self):
"""
Returns a snowflake.connection object
"""
conn_config = self._get_conn_params()
conn = connector.connect(**conn_config)
return conn
def __init__(self, base_directory, context):
"""
Initialisation method for the deployment. It can for example be used for loading modules that have to be kept in
memory or setting up connections. Load your external model files (such as pickles or .h5 files) here.
:param str base_directory: absolute path to the directory where the deployment.py file is located
:param dict context: a dictionary containing details of the deployment that might be useful in your code.
It contains the following keys:
- deployment (str): name of the deployment
- version (str): name of the version
- input_type (str): deployment input type, either 'structured' or 'plain'
- output_type (str): deployment output type, either 'structured' or 'plain'
- language (str): programming language the deployment is running
- environment_variables (str): the custom environment variables configured for the deployment.
You can also access those as normal environment variables via os.environ
"""
print("Initialising My Deployment")
print('Connecting to snowflake database')
SNOWFLAKE_ACCOUNT = os.environ.get('SNOWFLAKE_ACCOUNT')
SNOWFLAKE_USERNAME = os.environ.get('SNOWFLAKE_USERNAME')
SNOWFLAKE_PASSWORD = os.environ.get('SNOWFLAKE_PASSWORD')
SNOWFLAKE_DATABASE = os.environ.get('SNOWFLAKE_DATABASE')
try:
con = sf.connect(user=SNOWFLAKE_USERNAME,
password=SNOWFLAKE_PASSWORD,
account=SNOWFLAKE_ACCOUNT,
database=SNOWFLAKE_DATABASE)
self.cur = con.cursor()
except Exception as e:
print('There was a problem connecting to the database!')
print(e)
def run_snowflake(query: str,
role: str = SF_ROLE,
warehouse: str = SF_WAREHOUSE,
db: str = SF_DB,
schema: str = SF_SCHEMA):
"""
Runs queries on snowflake. Used for copy/create/delete/update statements affecting the snowflake database. Does not return data.
To query data from snowflake into a pandas dataframe use get_sf_data()
"""
con = sc.connect( #creates connection
user=SF_USER, password=SF_PASS, account=SF_ACCT)
cur = con.cursor(
) #creates cursor. You execute pretty much everything from the cursor
#First set up connection. role->warehouse->database->schema
cur.execute("USE role " + role)
cur.execute("USE warehouse " + warehouse)
cur.execute("USE DATABASE " + db)
cur.execute("USE SCHEMA " + schema)
#Execute query
cur.execute(query)
#close the cursor and then the connection when you're done.
cur.close
con.close
def clean_up(argv):
# Get the password from an appropriate environment variable, if
# available.
PASSWORD = os.getenv('SNOWSQL_PWD')
connection_parameters = args_to_properties(argv)
USER = connection_parameters["user"]
ACCOUNT = connection_parameters["account"]
WAREHOUSE = connection_parameters["warehouse"]
DATABASE = connection_parameters["database"]
SCHEMA = connection_parameters["schema"]
LARGEFILE = connection_parameters["largefile"]
# Cleanup the stage and close connection
conn = sf.connect(user=USER,
password=PASSWORD,
account=ACCOUNT,
warehouse=WAREHOUSE,
database=DATABASE,
schema=SCHEMA)
# Use role defined in function input
conn.cursor().execute('USE ROLE ACCOUNTADMIN')
sql = "remove @DDB_STG01/customer pattern ='.*.csv.gz'"
conn.cursor().execute(sql)
conn.close()
def sf_connect(
user:str,
account:str,
warehouse:str,
database:str,
schema:str,
password:str,
)->connect:
"""
Utilized to connect with Snowflake
:param: Use Credentials to Connect to Snowflake.
:return:
"""
try:
if password == None:
print("Service Account Password is required!")
return None
else:
con = connect(
user=user, account=account, password=password, warehouse=warehouse,
database=database, schema=schema
)
return con
except Exception as e:
print(f"Error while loading credentials: {e}")
def create_connection(self):
return connector.connect(user=self.username,
password=self.password,
account=self.account,
warehouse=self.warehouse,
database=self.database,
schema=self.schema)
def query(self, q):
# Note: This import will *break* the requests package in certain cases, guarding against it so that we only touch this odious libray when absolutely necessary (more info here: https://github.com/boto/boto3/issues/2577)
from snowflake.connector import DictCursor
from snowflake import connector
con = connector.connect(host=self.host,
user=self.user,
password=self.password,
account=self.account,
warehouse=self.warehouse,
database=self.database,
schema=self.schema,
protocol=self.protocol,
port=self.port,
application='MindsDB')
# Create a cursor object.
cur = con.cursor(DictCursor)
cur.execute(q)
#df = cur.fetch_pandas_all()
data = [x for x in cur.fetchall()]
df = pd.DataFrame(data)
cur.close()
con.close()
return df, self._make_colmap(df)
def __init__(self, p_acc: str, p_user: str, p_pwd: str, p_role: str,
p_db_name: str):
self.sf_connection = sf.connect(account=p_acc,
user=p_user,
password=p_pwd,
role=p_role,
database=p_db_name)
def __init__(
self,
warehouse='ADDS_DW',
database='CONTENT_ENGAGEMENT',
schema='ENGAGEMENT',
user=None,
password=None,
account=None,
):
# default to
if not (user or password or account):
user = environ['SNOWFLAKE_USER']
password = environ['SNOWFLAKE_PASS']
account = environ['SNOWFLAKE_ACCT']
self.connection = connector.connect(
user=user,
password=password,
account=account,
)
self.execute(f'USE WAREHOUSE {warehouse}')
self.execute(f'USE {database}')
self.execute(f'USE SCHEMA {schema}')
def query():
logging.config.dictConfig(settings.LOGGING)
log.info("Starting to test querying against Snowflake")
with connect(
user=settings.SNOWFLAKE_USER,
password=settings.SNOWFLAKE_PASSWORD,
account=settings.SNOWFLAKE_ACCOUNT,
database=settings.SNOWFLAKE_DATABASE,
role=settings.SNOWFLAKE_ROLE,
) as conn:
with conn.cursor() as cursor:
log.info("Switching to warehouse %s", settings.SNOWFLAKE_WAREHOUSE)
cursor.execute(
f"USE WAREHOUSE {settings.SNOWFLAKE_WAREHOUSE.upper()};")
log.info("Executing query")
cursor.execute(SQL)
log.info("Iterating over query results")
rows = 0
while True:
data = cursor.fetchmany(100_000)
log.info("Fetched %s rows", len(data))
if not data:
break
df = pandas.DataFrame(data, columns=cursor.description)
rows += df.shape[0]
log.info("Have iterated over %s rows so far", rows)
log.info("Finished query after %s", rows)
def connect(self, **kwargs) -> Snowmobile:
"""Establishes connection to Snowflake.
Re-implements `snowflake.connector.connect()` with connection
arguments sourced from snowmobile's object model, specifically:
* Credentials from `snowmobile.toml`.
* Default connection arguments from `snowmobile.toml`.
* Optional keyword arguments either passed to
:meth:`snowmobile.connect()` or directly to this method.
kwargs:
Optional keyword arguments to pass to
snowflake.connector.connect(); arguments passed here will
over-ride ``connection.default-arguments`` specified in
``snowmobile.toml``.
"""
try:
self.con = connect(
**{
**self.cfg.connection.connect_kwargs, # snowmobile.toml
**kwargs, # any kwarg over-rides
})
self.sql = sql.SQL(sn=self)
print(f"..connected: {str(self)}")
return self
except DatabaseError as e:
raise e
def run(self,
query: str = None,
data: tuple = None,
autocommit: bool = None):
"""
Task run method. Executes a query against snowflake database.
Args:
- query (str, optional): query to execute against database
- data (tuple, optional): values to use in query, must be specified using
placeholder is query string
- autocommit (bool, optional): set to True to autocommit, defaults to None
which takes the snowflake AUTOCOMMIT parameter
Returns:
- None
Raises:
- ValueError: if query parameter is None or a blank string
- DatabaseError: if exception occurs when executing the query
"""
if not query:
raise ValueError("A query string must be provided")
# build the connection parameter dictionary
# we will remove `None` values next
connect_params = {
"account": self.account,
"user": self.user,
"password": self.password,
"private_key": self.private_key,
"database": self.database,
"schema": self.schema,
"role": self.role,
"warehouse": self.warehouse,
"autocommit": self.autocommit,
}
# filter out unset values
connect_params = {
param: value
for (param, value) in connect_params.items() if value is not None
}
# connect to database, open cursor
conn = sf.connect(**connect_params)
# try to execute query
# context manager automatically rolls back failed transactions
try:
with conn:
with conn.cursor() as cursor:
executed = cursor.execute(query, params=data)
conn.close()
return executed
# pass through error, and ensure connection is closed
except Exception as error:
conn.close()
raise error
def run(self, metro: str = "usa") -> List[Trend]:
"""This task executes a service call to collect
trends for a provided metro area.
Parameters
----------
metro : str
Region/location to query
Returns
-------
List[Trend]
Array of trends for the given metro.
"""
# Extract WOE Id from mapping
woe_id = METRO_WOE_ID_MAP.get(metro)
assert isinstance(woe_id, int), "Invalid metro"
# Build client and fetch trends
client = self._build_client()
trends = client.trends_place(id=woe_id)
# Build Snowflake connection/cursor
snowflake_ctx = connector.connect(
account=SNOWFLAKE_ACCOUNT,
user=SNOWFLAKE_USER,
password=SNOWFLAKE_PASS,
database=SNOWFLAKE_DATABASE,
schema=SNOWFLAKE_SCHEMA,
)
cursor = snowflake_ctx.cursor()
sequence = list()
trend_list = list()
for trend in trends[0].get("trends", []):
date_created = datetime.datetime.now()
_trend = Trend(
date_created=date_created,
metro=metro,
woe=woe_id,
name=trend.get("name"),
url=trend.get("url"),
promoted=trend.get("promoted_content"),
querystring=trend.get("query"),
volume=trend.get("tweet_volume") or 0, # NOTE: Numeric type
)
trend_list.append(_trend)
sequence.append(_trend.sequence)
statement = """
INSERT INTO trends
(date_created, metro, woe, name, url, promoted, querystring, volume)
VALUES(%s, %s, %s, %s, %s, %s, %s, %s);
"""
# Execute insertion query
cursor.executemany(statement, sequence)
cursor.close()
return trend_list
def __init__(self):
self.conn = sf.connect(user=config.user,
password=config.password,
account=config.account,
warehouse=config.warehouse,
database=config.database,
role=config.role)
def sfconnect():
conn = connector.connect(account='lq20748.europe-west2.gcp',
user='******',
password='******',
warehouse='COMPUTE_WH',
database='DEMO_DB',
schema='PUBLIC')
return conn
def sf_connect():
con = connector.connect(user='******',
password='******',
account='jm95885.canada-central.azure',
warehouse='COMPUTE_WH',
database='DEMO_DB',
schema='PUBLIC')
return con
def __init__(self, username, password):
print("Connecting to snowflake...")
self.cnx = sf.connect(
user=username,
password=password,
account=config["ACCOUNT"])
self.cursor = self.cnx.cursor()
def new_conn(
config: Union[SFConfig, None] = None) -> snowflake.SnowflakeConnection:
"""
Creates a DBAPI connection object for Snowflake
"""
if config is None:
config = SFConfig()
return snowflake.connect(**asdict(config))
def __init__(self, user, password, account):
try:
self.connection = connect(user=user,
password=password,
account=account)
self.cursor = self.connection.cursor()
print("Snowflake connection established!")
except:
print("Connection Failed!")
def sfconnect():
cnx = connector.connect(
account='<Snowflake Account>', # Enter your snowflake account info
user='******', # Enter your snowflake user info
password='******', # Enter your snowflake acc password
warehouse='COMPUTE_WH',
database='DEMO_DB',
schema='PUBLIC')
return cnx
def _get_databases(cls, connector: 'SnowflakeConnector'):
# FIXME: Maybe use a generator instead of a list here?
with connector.connect() as connection:
return [
db['name']
# Fetch rows as dicts with column names as keys
for db in connection.cursor(DictCursor).execute('SHOW DATABASES').fetchall()
if 'name' in db
]
def sfconnect():
cnx = connector.connect(
account = ,
user = ,
password = ,
warehouse = 'COMPUTE_WH',
database = 'DEMO_DB',
schema = 'Public'
)
return cnx
def create_snowflake_connection() -> SnowflakeConnection:
return connect(
account=config.snowflakeAccount,
user=config.snowflakeUsername,
password=config.snowflakePassword,
role=config.snowflakeRole,
warehouse=config.snowflakeWarehouse,
database=config.snowflakeDatabase,
schema=config.snowflakeSchema,
)
def extract(table_name):
conn = connector.connect(user=user, password=password, account=account)
conn.cursor().execute(f"USE WAREHOUSE {warehouse}")
conn.cursor().execute(f"USE {database}")
conn.cursor().execute(f"USE SCHEMA {schema}")
query_output = conn.cursor().execute(f"""
select * from {table_name};
""")
query_output.fetch_pandas_all().to_csv(f"{table_name}{ondemand}.csv")
conn.close()
def create_connection(self, *args, **kwargs):
return connector.connect(
user=self.username,
password=self.password,
account=self.account,
warehouse=self.warehouse,
database=self.database,
schema=self.schema,
login_timeout=kwargs.get('connection_timeout_sec', DEFAULT_SOCKET_CONNECT_TIMEOUT),
)
def __init__(self):
self._db_connection = sf.connect(
user=os.getenv('SNOWFLAKE_USER'),
password=os.getenv('SNOWFLAKE_PASSWORD'),
account=os.getenv('SNOWFLAKE_ACCOUNT'),
database='RAW_ANALYTICS',
schema='DEV_SLIANG',
warehouse='LOADING',
session_parameters={
'QUERY_TAG': 'test',
})
self.cur = self._db_connection.cursor()
def __enter__(self):
"""
__enter__(self):
function - Unpack configuration credentials for given database
name and store into instance _connection variable. For use
with python context manager element.
returns - self
"""
self._connection = connect(**self._configuration[self.name])
return self
def connect(self):
self.log.debug(
"Establishing a new connection to Snowflake: account=%s, user=%s, database=%s, schema=%s, warehouse=%s, "
"role=%s, timeout=%s, authenticator=%s, ocsp_response_cache_filename=%s, proxy_host=%s, proxy_port=%s",
self._config.account,
self._config.user,
self._config.database,
self._config.schema,
self._config.warehouse,
self._config.role,
self._config.login_timeout,
self._config.authenticator,
self._config.ocsp_response_cache_filename,
self.proxy_host,
self.proxy_port,
)
try:
conn = sf.connect(
user=self._config.user,
password=self._config.password,
account=self._config.account,
database=self._config.database,
schema=self._config.schema,
warehouse=self._config.warehouse,
role=self._config.role,
passcode_in_password=self._config.passcode_in_password,
passcode=self._config.passcode,
client_prefetch_threads=self._config.client_prefetch_threads,
login_timeout=self._config.login_timeout,
ocsp_response_cache_filename=self._config.
ocsp_response_cache_filename,
authenticator=self._config.authenticator,
token=self._config.token,
client_session_keep_alive=self._config.client_keep_alive,
proxy_host=self.proxy_host,
proxy_port=self.proxy_port,
proxy_user=self.proxy_user,
proxy_password=self.proxy_password,
)
except Exception as e:
msg = "Unable to connect to Snowflake: {}".format(e)
self.service_check(self.SERVICE_CHECK_CONNECT,
self.CRITICAL,
message=msg,
tags=self._tags)
self.warning(msg)
else:
self.service_check(self.SERVICE_CHECK_CONNECT,
self.OK,
tags=self._tags)
self._conn = conn
def create_connection(self):
import snowflake.connector as sf
conn = sf.connect(
user=self.user,
password=self.password,
account=self.account_name
)
if self.warehouse is not None:
conn.cursor().execute("USE warehouse {};".format(self.warehouse))
if self.database is not None:
db_str = "USE {}".format(self.database)
if self.schema is not None:
db_str += ".{}".format(self.schema)
conn.cursor().execute(db_str)
return conn
