def _create_engine(self) -> Any:
with open(f"{ProjectConfig.hdm_home()}/{ProjectConfig.profile_path()}",
'r') as stream:
conn_conf = yaml.safe_load(stream)[ProjectConfig.hdm_env()][
self._connection_name]
return create_engine(
f"mysql+mysqlconnector://{conn_conf['user']}:{conn_conf['password']}@{conn_conf['host']}:"
f"{conn_conf['port']}/{conn_conf['database']}")
def _validate_configuration(self) -> bool:
with open(f"{ProjectConfig.hdm_home()}/{ProjectConfig.profile_path()}", 'r') as stream:
conn_conf = yaml.safe_load(stream)[ProjectConfig.hdm_env()][self._connection_name]
required_keys = ['host', 'port', 'database', 'user', 'password', 'driver']
is_valid = all([key in conn_conf.keys() for key in required_keys])
if is_valid:
required_keys = ['name', 'path']
return all([key in conn_conf['driver'].keys() for key in required_keys])
return is_valid
def _create_engine(self) -> Any:
with open(f"{ProjectConfig.hdm_home()}/{ProjectConfig.profile_path()}",
'r') as stream:
conn_conf = yaml.safe_load(stream)[ProjectConfig.hdm_env()][
self._connection_name]
if not conn_conf.get('dbpath', os.curdir):
db_path = os.path.abspath(os.curdir)
else:
db_path = os.path.abspath(conn_conf.get('dbpath', os.curdir))
database = str(conn_conf['database'])
if database.rfind(".db") == -1:
db_path = os.path.join(db_path, f"{database}.db")
return create_engine(f"sqlite:///{db_path}")
def _get_connection(self):
"""
Obtain a context managed azure connection
Returns: azure connection
Raises:
ConnectionError: azure connection could not be established
"""
with open(f"{ProjectConfig.hdm_home()}/{ProjectConfig.profile_path()}", 'r') as stream:
conn_conf = yaml.safe_load(stream)[ProjectConfig.hdm_env()][self._connection_name]
self._container = conn_conf['container_name']
connection = BlobServiceClient(account_url=conn_conf['url'], credential=conn_conf['sas'])
self._test_blob_container_existence(connection)
return connection
def _create_stage(self, stage_name, source_directory, connection):
# TODO: Check with John. How to get azure connection info- hardcoded below? Manefist or profile yml
with open(f"{ProjectConfig.hdm_home()}/{ProjectConfig.profile_path()}",
'r') as stream:
conn_conf = yaml.safe_load(stream)[ProjectConfig.hdm_env(
)]['azure'] # Fai: Pull from manifest "azure_env" the azure env name here instead of hardcode
url = conn_conf['url'].replace("https",
"azure") + self._stage_directory
self.__external_stage_params = f"URL = '{url}' CREDENTIALS = ( AZURE_SAS_TOKEN = '{conn_conf['sas']}')"
cursor = connection.cursor()
# create stage
self._logger.info("Creating stage: %s", self._stage_name)
create_stage_sql = f"CREATE OR REPLACE STAGE {self._stage_name}"
create_stage_sql += f" {self.__external_stage_params}"
cursor.execute(create_stage_sql)
cursor.close()
def _get_connection(self):
"""
Obtain a context managed netezza connection
Returns: Netezza connection
Raises:
ConnectionError: Netezza connection could not be established
"""
connection = None
with open(f"{ProjectConfig.hdm_home()}/{ProjectConfig.profile_path()}", 'r') as stream:
conn_conf = yaml.safe_load(stream)[ProjectConfig.hdm_env()][self._connection_name]
connection_config = self._get_connection_config(config=conn_conf)
connection_invalid = True
connection_attempt_count = 0
timeout = self._timeout
while connection_attempt_count < self._max_attempts:
connection = self._connect_by_connector(connection_config)
# If your connection is valid, then set it so and break from while loop
if self._test_connection(connection):
connection_invalid = False
break
# Otherwise, you must put program to sleep, wait for next time to obtain connection and carry on.
connection_attempt_count += 1
if connection_invalid < self._max_attempts:
time.sleep(timeout)
timeout *= self._timeout_factor
if connection_invalid:
raise ConnectionError('Unable to connection to Netezza. Please try again.')
yield connection
connection.close()
def _create_engine(self) -> Any:
"""
get ODBC connection string from azure portal
'Driver={ODBC Driver 13 for SQL Server};Server=tcp:yourDBServerName.database.windows.net,1433;
Database=dbname;Uid=username;Pwd=xxx;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;
and populate in values in hdm_profile.yml
Important: {ODBC Driver 17 for SQL Server} works even though in azure portal connection string it say
ODBC Driver 13 for SQL Server
"""
with open(f"{ProjectConfig.hdm_home()}/{ProjectConfig.profile_path()}",
'r') as stream:
conn_conf = yaml.safe_load(stream)[ProjectConfig.hdm_env()][
self._connection_name]
conn = f"Driver={{{conn_conf['driver']}}};Server=tcp:{conn_conf['host']}.database.windows.net,{conn_conf['port']};" \
f"Database={conn_conf['database']};Uid={conn_conf['user']};Pwd={conn_conf['password']};Encrypt=yes;" \
f"TrustServerCertificate=no;Connection Timeout=30;"
params = urllib.parse.quote_plus(conn)
conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
return create_engine(conn_str)
def _get_connection(self, **kwargs):
"""
Gets a S3 Session based on env variables.
Connection is made using credentials specified in .aws/ or as per the profile values specified in:
aws_access_key_id
aws_secret_access_key
region_name
"""
with open(f"{ProjectConfig.hdm_home()}/{ProjectConfig.profile_path()}",
'r') as stream:
conn_conf = yaml.safe_load(stream)[ProjectConfig.hdm_env()][
kwargs.get('connection')]
# TODO - Validation needed for all the correct keys present or not
if 'profile' in conn_conf.keys():
connection = boto3.session.Session(
profile_name=conn_conf.get('profile'))
else:
connection = boto3.session.Session(
aws_access_key_id=conn_conf.get('aws_access_key_id'),
aws_secret_access_key=conn_conf.get('aws_secret_access_key'),
region_name=conn_conf.get('region_name'))
return connection
def test_hdm_env(self):
self.assertEqual('unit-test', ProjectConfig.hdm_env())
os.environ['HDM_ENV'] = 'prod'
self.assertEqual('prod', ProjectConfig.hdm_env())