def get_sample_data(self,
snippet,
database=None,
table=None,
column=None,
is_async=False,
operation=None):
if self.db is None:
raise AuthenticationRequired()
assist = self._createAssist(self.db)
response = {'status': -1, 'result': {}}
sample_data, description = assist.get_sample_data(
database, table, column)
if sample_data or description:
response['status'] = 0
response['headers'] = [col[0] for col in description
] if description else []
response['full_headers'] = [{
'name': col[0],
'type': col[1],
'comment': ''
} for col in description]
response['rows'] = sample_data if sample_data else []
else:
response['message'] = _('Failed to get sample data.')
return response
def execute(self, notebook, snippet):
if self.db is None:
raise AuthenticationRequired()
data, description = query_and_fetch(self.db, snippet['statement'],
1000)
has_result_set = data is not None
return {
'sync': True,
'has_result_set': has_result_set,
'result': {
'has_more':
False,
'data':
data if has_result_set else [],
'meta': [{
'name': col[0],
'type': col[1],
'comment': ''
} for col in description] if has_result_set else [],
'type':
'table'
}
}
def autocomplete(self,
snippet,
database=None,
table=None,
column=None,
nested=None):
if self.db is None:
raise AuthenticationRequired()
assist = Assist(self.db)
response = {'status': -1}
if database is None:
response['databases'] = assist.get_databases()
elif table is None:
response['tables'] = assist.get_tables(database)
response['tables_meta'] = response['tables']
else:
columns = assist.get_columns(database, table)
response['columns'] = [col[0] for col in columns]
response['extended_columns'] = [{
'name': col[0],
'type': col[1],
'comment': col[5]
} for col in columns]
response['status'] = 0
return response
def autocomplete(self, snippet, database=None, table=None, column=None, nested=None):
if self.db is None:
raise AuthenticationRequired()
assist = Assist(self.db)
response = {'error': 0}
try:
if database is None:
response['databases'] = assist.get_databases()
elif table is None:
response['tables'] = assist.get_tables(database)
else:
columns = assist.get_columns(database, table)
response['columns'] = [col[0] for col in columns]
response['extended_columns'] = [{
'name': col[0],
'type': col[1],
'comment': col[5]
} for col in columns
]
except Exception, e:
LOG.warn('Autocomplete data fetching error: %s' % e)
response['code'] = -1
response['error'] = str(e)
def autocomplete(self,
snippet,
database=None,
table=None,
column=None,
nested=None):
if self.db is None:
raise AuthenticationRequired()
assist = self._createAssist(self.db)
response = {'status': -1}
if database is None:
response['databases'] = assist.get_databases()
elif table is None:
tables = assist.get_tables_full(database)
response['tables'] = [table['name'] for table in tables]
response['tables_meta'] = tables
else:
columns = assist.get_columns_full(database, table)
response['columns'] = [col['name'] for col in columns]
response['extended_columns'] = columns
response['status'] = 0
return response
def _create_engine(self):
if '${' in self.options['url']: # URL parameters substitution
auth_provided = False
vars = {'USER': self.user.username}
if 'session' in self.options:
for _prop in self.options['session']['properties']:
if _prop['name'] == 'user':
vars['USER'] = _prop['value']
auth_provided = True
if _prop['name'] == 'password':
vars['PASSWORD'] = _prop['value']
auth_provided = True
if not auth_provided:
raise AuthenticationRequired(
message='Missing username and/or password')
raw_url = Template(self.options['url'])
url = raw_url.safe_substitute(**vars)
else:
url = self.options['url']
if url.startswith('awsathena+rest://'):
url = url.replace(url[17:37], urllib_quote_plus(url[17:37]))
url = url.replace(url[38:50], urllib_quote_plus(url[38:50]))
s3_staging_dir = url.rsplit('s3_staging_dir=', 1)[1]
url = url.replace(s3_staging_dir,
urllib_quote_plus(s3_staging_dir))
options = self.options.copy()
options.pop('session', None)
options.pop('url', None)
return create_engine(url, **options)
def _create_engine(self):
if '${' in self.options['url']: # URL parameters substitution
vars = {'USER': self.user.username}
if '${PASSWORD}' in self.options['url']:
auth_provided = False
if 'session' in self.options:
for _prop in self.options['session']['properties']:
if _prop['name'] == 'user':
vars['USER'] = _prop['value']
auth_provided = True
if _prop['name'] == 'password':
vars['PASSWORD'] = _prop['value']
auth_provided = True
if not auth_provided:
raise AuthenticationRequired(
message='Missing username and/or password')
raw_url = Template(self.options['url'])
url = raw_url.safe_substitute(**vars)
else:
url = self.options['url']
if url.startswith('awsathena+rest://'):
url = url.replace(url[17:37], urllib_quote_plus(url[17:37]))
url = url.replace(url[38:50], urllib_quote_plus(url[38:50]))
s3_staging_dir = url.rsplit('s3_staging_dir=', 1)[1]
url = url.replace(s3_staging_dir,
urllib_quote_plus(s3_staging_dir))
if self.options.get('credentials_json'):
self.options['credentials_info'] = json.loads(
self.options.pop('credentials_json'))
# Enables various SqlAlchemy args to be passed along for both Hive & Presto connectors
# Refer to SqlAlchemy pyhive for more details
if self.options.get('connect_args'):
self.options['connect_args'] = json.loads(
self.options.pop('connect_args'))
if self.options.get('has_impersonation'):
self.options.setdefault('connect_args',
{}).setdefault('principal_username',
self.user.username)
options = self.options.copy()
options.pop('session', None)
options.pop('url', None)
options.pop('has_ssh', None)
options.pop('has_impersonation', None)
options.pop('ssh_server_host', None)
options['pool_pre_ping'] = not url.startswith(
'phoenix://'
) # Should be moved to dialect when connectors always on
return create_engine(url, **options)
def _create_engine(self):
if '${' in self.options['url']: # URL parameters substitution
vars = {'USER': self.user.username}
if '${PASSWORD}' in self.options['url']:
auth_provided = False
if 'session' in self.options:
for _prop in self.options['session']['properties']:
if _prop['name'] == 'user':
vars['USER'] = _prop['value']
auth_provided = True
if _prop['name'] == 'password':
vars['PASSWORD'] = _prop['value']
auth_provided = True
if not auth_provided:
raise AuthenticationRequired(
message='Missing username and/or password')
raw_url = Template(self.options['url'])
url = raw_url.safe_substitute(**vars)
else:
url = self.options['url']
if url.startswith('awsathena+rest://'):
url = url.replace(url[17:37], urllib_quote_plus(url[17:37]))
url = url.replace(url[38:50], urllib_quote_plus(url[38:50]))
s3_staging_dir = url.rsplit('s3_staging_dir=', 1)[1]
url = url.replace(s3_staging_dir,
urllib_quote_plus(s3_staging_dir))
if self.options.get('has_impersonation'):
m = re.search(URL_PATTERN, url)
driver_name = m.group('driver_name')
if not driver_name:
raise QueryError(
'Driver name of %(url)s could not be found and impersonation is turned on'
% {'url': url})
url = url.replace(
driver_name, '%(driver_name)s%(username)s@' % {
'driver_name': driver_name,
'username': self.user.username
})
if self.options.get('credentials_json'):
self.options['credentials_info'] = json.loads(
self.options.pop('credentials_json'))
options = self.options.copy()
options.pop('session', None)
options.pop('url', None)
options.pop('has_ssh', None)
options.pop('has_impersonation', None)
options.pop('ssh_server_host', None)
return create_engine(url, **options)
def decorator(*args, **kwargs):
try:
return func(*args, **kwargs)
except OperationalError, e:
message = str(e)
if '1045' in message: # 'Access denied' # MySQL
raise AuthenticationRequired(message=message)
else:
raise e
def _create_connection(self, engine):
connection = None
try:
connection = engine.connect()
except Exception as e:
engine_key = self._get_engine_key()
ENGINES.pop(engine_key, None)
raise AuthenticationRequired(message='Could not establish connection to datasource: %s' % e)
return connection
def test_connection(self, throw_exception=True):
try:
self.connect()
return True
except Exception, e:
message = force_unicode(smart_str(e))
if throw_exception:
if 'Access denied' in message:
raise AuthenticationRequired()
raise
else:
return False
def query_error_handler(func):
def decorator(*args, **kwargs):
try:
return func(*args, **kwargs)
except AuthenticationRequired, e:
raise e
except Exception, e:
message = force_unicode(smart_str(e))
if 'error occurred while trying to connect to the Java server' in message:
raise QueryError(_('%s: is the DB Proxy server running?') % message)
elif 'Access denied' in message:
raise AuthenticationRequired()
else:
raise QueryError(message)
def decorator(*args, **kwargs):
try:
return func(*args, **kwargs)
except OperationalError as e:
message = str(e)
if '1045' in message: # 'Access denied' # MySQL
raise AuthenticationRequired(message=message)
else:
raise e
except Exception as e:
message = force_unicode(e)
if 'Invalid query handle' in message or 'Invalid OperationHandle' in message:
raise QueryExpired(e)
else:
LOG.exception('Query Error')
raise QueryError(message)
def create_session(self, lang=None, properties=None):
global API_CACHE
props = super(JdbcApi, self).create_session(lang, properties)
properties = dict([(p['name'], p['value']) for p in properties]) if properties is not None else {}
props['properties'] = {} # We don't store passwords
if self.db is None:
if 'password' in properties:
user = properties.get('user') or self.options.get('user')
props['properties'] = {'user': user}
self.db = API_CACHE[self.cache_key] = Jdbc(self.options['driver'], self.options['url'], user, properties.pop('password'))
if self.db is None:
raise AuthenticationRequired()
return props
def get_sample_data(self, snippet, database=None, table=None):
if self.db is None:
raise AuthenticationRequired()
assist = Assist(self.db)
response = {'status': -1}
sample_data, description = assist.get_sample_data(database, table)
if sample_data:
response['status'] = 0
response['headers'] = [col[0] for col in description
] if description else []
response['rows'] = sample_data
else:
response['message'] = _('Failed to get sample data.')
return response
def query_and_fetch(db, statement, n=None):
data = None
try:
db.connect()
curs = db.cursor()
try:
if curs.execute(statement):
data = curs.fetchmany(n)
meta = curs.description
return data, meta
finally:
curs.close()
except Exception, e:
message = force_unicode(smart_str(e))
if 'Access denied' in message:
raise AuthenticationRequired()
raise
class JdbcApi(Api):
def __init__(self, user, interpreter=None):
global API_CACHE
Api.__init__(self, user, interpreter=interpreter)
self.db = None
self.options = interpreter['options']
if self.cache_key in API_CACHE:
self.db = API_CACHE[self.cache_key]
elif 'password' in self.options:
username = self.options.get('user') or user.username
impersonation_property = self.options.get('impersonation_property')
self.db = API_CACHE[self.cache_key] = Jdbc(
self.options['driver'],
self.options['url'],
username,
self.options['password'],
impersonation_property=impersonation_property,
impersonation_user=user.username)
def create_session(self, lang=None, properties=None):
global API_CACHE
props = super(JdbcApi, self).create_session(lang, properties)
properties = dict([(p['name'], p['value']) for p in properties
]) if properties is not None else {}
props['properties'] = {} # We don't store passwords
if self.db is None or not self.db.test_connection(
throw_exception='password' not in properties):
if 'password' in properties:
user = properties.get('user') or self.options.get('user')
props['properties'] = {'user': user}
self.db = API_CACHE[self.cache_key] = Jdbc(
self.options['driver'], self.options['url'], user,
properties.pop('password'))
self.db.test_connection(throw_exception=True)
if self.db is None:
raise AuthenticationRequired()
return props
@query_error_handler
def execute(self, notebook, snippet):
if self.db is None:
raise AuthenticationRequired()
data, description = query_and_fetch(self.db, snippet['statement'],
1000)
has_result_set = data is not None
return {
'sync': True,
'has_result_set': has_result_set,
'result': {
'has_more':
False,
'data':
data if has_result_set else [],
'meta': [{
'name': col[0],
'type': col[1],
'comment': ''
} for col in description] if has_result_set else [],
'type':
'table'
}
}
@query_error_handler
def check_status(self, notebook, snippet):
return {'status': 'available'}
def _fetch_result(self, cursor):
return {}
@query_error_handler
def fetch_result_metadata(self):
pass
@query_error_handler
def cancel(self, notebook, snippet):
return {'status': 0}
def download(self, notebook, snippet, format, user_agent=None):
raise PopupException('Downloading is not supported yet')
def progress(self, snippet, logs):
return 50
@query_error_handler
def close_statement(self, snippet):
return {'status': -1}
@query_error_handler
def autocomplete(self,
snippet,
database=None,
table=None,
column=None,
nested=None):
if self.db is None:
raise AuthenticationRequired()
assist = Assist(self.db)
response = {'status': -1}
if database is None:
response['databases'] = assist.get_databases()
elif table is None:
response['tables'] = assist.get_tables(database)
response['tables_meta'] = response['tables']
else:
columns = assist.get_columns(database, table)
response['columns'] = [col[0] for col in columns]
response['extended_columns'] = [{
'name': col[0],
'type': col[1],
'comment': col[5]
} for col in columns]
response['status'] = 0
return response
@query_error_handler
def get_sample_data(self,
snippet,
database=None,
table=None,
column=None,
async=False):
if self.db is None:
raise AuthenticationRequired()
assist = Assist(self.db)
response = {'status': -1}
sample_data, description = assist.get_sample_data(
database, table, column)
if sample_data:
response['status'] = 0
response['headers'] = [col[0] for col in description
] if description else []
response['rows'] = sample_data
else:
response['message'] = _('Failed to get sample data.')
return response
elif table is None:
tables = assist.get_tables_full(database)
response['tables'] = [table['name'] for table in tables]
response['tables_meta'] = tables
else:
columns = assist.get_columns_full(database, table)
response['columns'] = [col['name'] for col in columns]
response['extended_columns'] = columns
response['status'] = 0
return response
@query_error_handler
def get_sample_data(self, snippet, database=None, table=None, column=None, async=False, operation=None):
if self.db is None:
raise AuthenticationRequired()
assist = self._createAssist(self.db)
response = {'status': -1, 'result': {}}
sample_data, description = assist.get_sample_data(database, table, column)
if sample_data or description:
response['status'] = 0
response['headers'] = [col[0] for col in description] if description else []
response['full_headers'] = [{
'name': col[0],
'type': col[1],
'comment': ''
} for col in description]
response['rows'] = sample_data if sample_data else []