def _generate_mail_content(schedule, screenshot, name, url):
if schedule.delivery_type == EmailDeliveryType.attachment:
images = None
data = {
'screenshot.png': screenshot,
}
body = __(
'<b><a href="%(url)s">Explore in Superset</a></b><p></p>',
name=name,
url=url,
)
elif schedule.delivery_type == EmailDeliveryType.inline:
# Get the domain from the 'From' address ..
# and make a message id without the < > in the ends
domain = parseaddr(config.get('SMTP_MAIL_FROM'))[1].split('@')[1]
msgid = make_msgid(domain)[1:-1]
images = {
msgid: screenshot,
}
data = None
body = __(
"""
<b><a href="%(url)s">Explore in Superset</a></b><p></p>
<img src="cid:%(msgid)s">
""",
name=name, url=url, msgid=msgid,
)
return EmailContent(body, data, images)
def user_reset(token=None):
user_db = model.User.get_by('token', token)
if not user_db:
flask.flash(__('That link is either invalid or expired.'), category='danger')
return flask.redirect(flask.url_for('welcome'))
if auth.is_logged_in():
flask_login.logout_user()
return flask.redirect(flask.request.path)
form = UserResetForm()
if form.validate_on_submit():
user_db.password_hash = util.password_hash(user_db, form.new_password.data)
user_db.token = util.uuid()
user_db.verified = True
user_db.put()
flask.flash(__('Your password was changed succesfully.'), category='success')
return auth.signin_user_db(user_db)
return flask.render_template(
'user/user_reset.html',
title='Reset Password',
html_class='user-reset',
form=form,
user_db=user_db,
)
def user_verify(token):
user_db = auth.current_user_db()
if user_db.token != token:
flask.flash(__('That link is either invalid or expired.'), category='danger')
return flask.redirect(flask.url_for('profile'))
user_db.verified = True
user_db.token = util.uuid()
user_db.put()
flask.flash(__('Hooray! Your email is now verified.'), category='success')
return flask.redirect(flask.url_for('profile'))
def _get_slice_data(schedule):
slc = schedule.slice
slice_url = _get_url_path(
'Superset.explore_json',
csv='true',
form_data=json.dumps({'slice_id': slc.id}),
)
# URL to include in the email
url = _get_url_path(
'Superset.slice',
slice_id=slc.id,
)
cookies = {}
for cookie in _get_auth_cookies():
cookies['session'] = cookie
response = requests.get(slice_url, cookies=cookies)
response.raise_for_status()
# TODO: Move to the csv module
rows = [r.split(b',') for r in response.content.splitlines()]
if schedule.delivery_type == EmailDeliveryType.inline:
data = None
# Parse the csv file and generate HTML
columns = rows.pop(0)
with app.app_context():
body = render_template(
'superset/reports/slice_data.html',
columns=columns,
rows=rows,
name=slc.slice_name,
link=url,
)
elif schedule.delivery_type == EmailDeliveryType.attachment:
data = {
__('%(name)s.csv', name=slc.slice_name): response.content,
}
body = __(
'<b><a href="%(url)s">Explore in Superset</a></b><p></p>',
name=slc.slice_name,
url=url,
)
return EmailContent(body, data, None)
def signin():
next_url = util.get_next_url()
form = None
if config.CONFIG_DB.has_email_authentication:
form = form_with_recaptcha(SignInForm())
save_request_params()
if form.validate_on_submit():
result = get_user_db_from_email(form.email.data, form.password.data)
if result:
cache.reset_auth_attempt()
return signin_user_db(result)
if result is None:
form.email.errors.append(__('Email or Password do not match'))
if result is False:
return flask.redirect(flask.url_for('welcome'))
if not form.errors:
form.next_url.data = next_url
if form and form.errors:
cache.bump_auth_attempt()
return flask.render_template(
'auth/auth.html',
title=_('Sign in'),
html_class='auth',
next_url=next_url,
form=form,
form_type='signin' if config.CONFIG_DB.has_email_authentication else '',
**urls_for_oauth(next_url)
)
def user_activate(token):
if auth.is_logged_in():
flask_login.logout_user()
return flask.redirect(flask.request.path)
user_db = model.User.get_by('token', token)
if not user_db:
flask.flash(__('That link is either invalid or expired.'), category='danger')
return flask.redirect(flask.url_for('welcome'))
form = UserActivateForm(obj=user_db)
if form.validate_on_submit():
form.populate_obj(user_db)
user_db.password_hash = util.password_hash(user_db, form.password.data)
user_db.token = util.uuid()
user_db.verified = True
user_db.put()
return auth.signin_user_db(user_db)
return flask.render_template(
'user/user_activate.html',
title='Activate Account',
html_class='user-activate',
user_db=user_db,
form=form,
)
def signup():
next_url = util.get_next_url()
form = None
if config.CONFIG_DB.has_email_authentication:
form = form_with_recaptcha(SignUpForm())
save_request_params()
if form.validate_on_submit():
user_db = model.User.get_by('email', form.email.data)
if user_db:
form.email.errors.append(__('This email is already taken.'))
if not form.errors:
user_db = create_user_db(
None,
util.create_name_from_email(form.email.data),
form.email.data,
form.email.data,
)
user_db.put()
task.activate_user_notification(user_db)
cache.bump_auth_attempt()
return flask.redirect(flask.url_for('welcome'))
if form and form.errors:
cache.bump_auth_attempt()
title = _('Sign up') if config.CONFIG_DB.has_email_authentication else _('Sign in')
return flask.render_template(
'auth/auth.html',
title=title,
html_class='auth',
next_url=next_url,
form=form,
**urls_for_oauth(next_url)
)
def notify_user_about_perm_udate(
granter, user, role, datasource, tpl_name, config):
msg = render_template(tpl_name, granter=granter, user=user, role=role,
datasource=datasource)
logging.info(msg)
subject = __('[Superset] Access to the datasource %(name)s was granted',
name=datasource.full_name)
send_email_smtp(user.email, subject, msg, config, bcc=granter.email,
dryrun=config.get('EMAIL_NOTIFICATIONS'))
def _register_schedule_menus():
appbuilder.add_separator('Manage')
appbuilder.add_view(
DashboardEmailScheduleView,
'Dashboard Email Schedules',
label=__('Dashboard Emails'),
category='Manage',
category_label=__('Manage'),
icon='fa-search')
appbuilder.add_view(
SliceEmailScheduleView,
'Chart Emails',
label=__('Chart Email Schedules'),
category='Manage',
category_label=__('Manage'),
icon='fa-search')
def facebook_authorized():
response = facebook.authorized_response()
if response is None:
flask.flash(__('You denied the request to sign in.'))
return flask.redirect(util.get_next_url())
flask.session['oauth_token'] = (response['access_token'], '')
me = facebook.get('/me?fields=name,email')
user_db = retrieve_user_from_facebook(me.data)
return auth.signin_user_db(user_db)
def google_authorized():
response = google.authorized_response()
if response is None:
flask.flash(__('You denied the request to sign in.'))
return flask.redirect(util.get_next_url())
flask.session['oauth_token'] = (response['access_token'], '')
me = google.get('me', data={'access_token': response['access_token']})
user_db = retrieve_user_from_google(me.data)
return auth.signin_user_db(user_db)
def twitter_authorized():
response = twitter.authorized_response()
if response is None:
flask.flash(__('You denied the request to sign in.'))
return flask.redirect(util.get_next_url())
flask.session['oauth_token'] = (
response['oauth_token'],
response['oauth_token_secret'],
)
user_db = retrieve_user_from_twitter(response)
return auth.signin_user_db(user_db)
def deliver_dashboard(schedule):
"""
Given a schedule, delivery the dashboard as an email report
"""
dashboard = schedule.dashboard
dashboard_url = _get_url_path(
'Superset.dashboard',
dashboard_id=dashboard.id,
)
# Create a driver, fetch the page, wait for the page to render
driver = create_webdriver()
window = config.get('WEBDRIVER_WINDOW')['dashboard']
driver.set_window_size(*window)
driver.get(dashboard_url)
time.sleep(PAGE_RENDER_WAIT)
# Set up a function to retry once for the element.
# This is buggy in certain selenium versions with firefox driver
get_element = getattr(driver, 'find_element_by_class_name')
element = retry_call(
get_element,
fargs=['grid-container'],
tries=2,
delay=PAGE_RENDER_WAIT,
)
try:
screenshot = element.screenshot_as_png
except WebDriverException:
# Some webdrivers do not support screenshots for elements.
# In such cases, take a screenshot of the entire page.
screenshot = driver.screenshot() # pylint: disable=no-member
finally:
destroy_webdriver(driver)
# Generate the email body and attachments
email = _generate_mail_content(
schedule,
screenshot,
dashboard.dashboard_title,
dashboard_url,
)
subject = __(
'%(prefix)s %(title)s',
prefix=config.get('EMAIL_REPORTS_SUBJECT_PREFIX'),
title=dashboard.dashboard_title,
)
_deliver_email(schedule, subject, email)
def signin_user_db(user_db):
if not user_db:
return flask.redirect(flask.url_for('signin'))
flask_user_db = FlaskUser(user_db)
auth_params = flask.session.get('auth-params', {
'next': flask.url_for('welcome'),
'remember': False,
})
flask.session.pop('auth-params', None)
if flask_login.login_user(flask_user_db, remember=auth_params['remember']):
user_db.put_async()
return util.set_locale(
user_db.locale,
flask.redirect(util.get_next_url(auth_params['next'])),
)
flask.flash(__('Sorry, but you could not sign in.'), category='danger')
return flask.redirect(flask.url_for('signin'))
def deliver_slice(schedule):
"""
Given a schedule, delivery the slice as an email report
"""
if schedule.email_format == SliceEmailReportFormat.data:
email = _get_slice_data(schedule)
elif schedule.email_format == SliceEmailReportFormat.visualization:
email = _get_slice_visualization(schedule)
else:
raise RuntimeError('Unknown email report format')
subject = __(
'%(prefix)s %(title)s',
prefix=config.get('EMAIL_REPORTS_SUBJECT_PREFIX'),
title=schedule.slice.slice_name,
)
_deliver_email(schedule, subject, email)
def save(self):
datasource = json.loads(request.form.get('data'))
datasource_id = datasource.get('id')
datasource_type = datasource.get('type')
orm_datasource = ConnectorRegistry.get_datasource(
datasource_type, datasource_id, db.session)
if not check_ownership(orm_datasource, raise_if_false=False):
return json_error_response(
__(
'You are not authorized to modify '
'this data source configuration'),
status='401',
)
orm_datasource.update_from_object(datasource)
data = orm_datasource.data
db.session.commit()
return self.json_response(data)
def feedback():
if not config.CONFIG_DB.feedback_email:
return flask.abort(418)
form = FeedbackForm(obj=auth.current_user_db())
if not config.CONFIG_DB.has_anonymous_recaptcha or auth.is_logged_in():
del form.recaptcha
if form.validate_on_submit():
body = '%s\n\n%s' % (form.message.data, form.email.data)
kwargs = {'reply_to': form.email.data} if form.email.data else {}
task.send_mail_notification('%s...' % body[:48].strip(), body, **kwargs)
flask.flash(__('Thank you for your feedback!'), category='success')
return flask.redirect(flask.url_for('welcome'))
return flask.render_template(
'feedback.html',
title=_('Feedback'),
html_class='feedback',
form=form,
)
def test_deliver_slice_csv_attachment(self, send_email_smtp, get):
response = Mock()
get.return_value = response
response.raise_for_status.return_value = None
response.content = self.CSV
schedule = db.session.query(SliceEmailSchedule).filter_by(
id=self.slice_schedule).all()[0]
schedule.email_format = SliceEmailReportFormat.data
schedule.delivery_type = EmailDeliveryType.attachment
deliver_slice(schedule)
send_email_smtp.assert_called_once()
file_name = __('%(name)s.csv', name=schedule.slice.slice_name)
self.assertEquals(
send_email_smtp.call_args[1]['data'][file_name],
self.CSV,
)
from ..models.connector import AppsFlyerConnector
# for show all Connectors in system
class AppsFlyerConnectorView(SupersetModelView, DeleteMixin):
"""View For Connector Model."""
datamodel = SQLAInterface(AppsFlyerConnector)
list_columns = ['name', 'app_id', 'admin_data_sources']
add_columns = ['name', 'app_id', 'api_token', 'url_pat']
edit_columns = add_columns
@action('web_test', 'web_test', 'web_test', 'fa-rocket')
def web_test(self, items):
if not isinstance(items, list):
items = [items]
items[0].web_test()
return redirect('appsflyerconnectorview/list/')
# Register ConnectorView Model View
appbuilder.add_view(AppsFlyerConnectorView,
'AppsFlyer',
icon='fa-random',
category='Connectors',
category_icon='fa-rocket',
category_label=__('Connectors'))
class DashboardModelView(DashboardMixin, SupersetModelView, DeleteMixin): # pylint: disable=too-many-ancestors
route_base = "/dashboard"
datamodel = SQLAInterface(DashboardModel)
# TODO disable api_read and api_delete (used by cypress)
# once we move to ChartRestModelApi
class_permission_name = "Dashboard"
method_permission_name = MODEL_VIEW_RW_METHOD_PERMISSION_MAP
include_route_methods = RouteMethod.CRUD_SET | {
RouteMethod.API_READ,
RouteMethod.API_DELETE,
"download_dashboards",
}
@has_access
@expose("/list/")
def list(self) -> FlaskResponse:
if not is_feature_enabled("ENABLE_REACT_CRUD_VIEWS"):
return super().list()
return super().render_app_template()
@action("mulexport", __("Export"), __("Export dashboards?"), "fa-database")
def mulexport( # pylint: disable=no-self-use
self, items: Union["DashboardModelView",
List["DashboardModelView"]]) -> FlaskResponse:
if not isinstance(items, list):
items = [items]
ids = "".join("&id={}".format(d.id) for d in items)
return redirect("/dashboard/export_dashboards_form?{}".format(ids[1:]))
@event_logger.log_this
@has_access
@expose("/export_dashboards_form")
def download_dashboards(self) -> FlaskResponse:
if request.args.get("action") == "go":
ids = request.args.getlist("id")
return Response(
DashboardModel.export_dashboards(ids),
headers=generate_download_headers("json"),
mimetype="application/text",
)
return self.render_template("superset/export_dashboards.html",
dashboards_url="/dashboard/list")
def pre_add(self, item: "DashboardModelView") -> None:
item.slug = item.slug or None
if item.slug:
item.slug = item.slug.strip()
item.slug = item.slug.replace(" ", "-")
item.slug = re.sub(r"[^\w\-]+", "", item.slug)
if g.user not in item.owners:
item.owners.append(g.user)
utils.validate_json(item.json_metadata)
utils.validate_json(item.position_json)
owners = list(item.owners)
for slc in item.slices:
slc.owners = list(set(owners) | set(slc.owners))
def pre_update(self, item: "DashboardModelView") -> None:
check_ownership(item)
self.pre_add(item)
def get_datasource_exist_error_msg(full_name):
return __("Datasource %(name)s already exists", name=full_name)
class TableModelView(DatasourceModelView, DeleteMixin, YamlExportMixin): # noqa
datamodel = SQLAInterface(models.SqlaTable)
list_title = _('List Tables')
show_title = _('Show Table')
add_title = _('Import a table definition')
edit_title = _('Edit Table')
list_columns = [
'link', 'database_name',
'changed_by_', 'modified']
order_columns = ['modified']
add_columns = ['database', 'schema', 'table_name']
edit_columns = [
'table_name', 'sql', 'filter_select_enabled',
'fetch_values_predicate', 'database', 'schema',
'description', 'owners',
'main_dttm_col', 'default_endpoint', 'offset', 'cache_timeout',
'is_sqllab_view', 'template_params',
]
base_filters = [['id', DatasourceFilter, lambda: []]]
show_columns = edit_columns + ['perm', 'slices']
related_views = [TableColumnInlineView, SqlMetricInlineView]
base_order = ('changed_on', 'desc')
search_columns = (
'database', 'schema', 'table_name', 'owners', 'is_sqllab_view',
)
description_columns = {
'slices': _(
'The list of charts associated with this table. By '
'altering this datasource, you may change how these associated '
'charts behave. '
'Also note that charts need to point to a datasource, so '
'this form will fail at saving if removing charts from a '
'datasource. If you want to change the datasource for a chart, '
"overwrite the chart from the 'explore view'"),
'offset': _('Timezone offset (in hours) for this datasource'),
'table_name': _(
'Name of the table that exists in the source database'),
'schema': _(
'Schema, as used only in some databases like Postgres, Redshift '
'and DB2'),
'description': Markup(
'Supports <a href="https://daringfireball.net/projects/markdown/">'
'markdown</a>'),
'sql': _(
'This fields acts a Superset view, meaning that Superset will '
'run a query against this string as a subquery.',
),
'fetch_values_predicate': _(
'Predicate applied when fetching distinct value to '
'populate the filter control component. Supports '
'jinja template syntax. Applies only when '
'`Enable Filter Select` is on.',
),
'default_endpoint': _(
'Redirects to this endpoint when clicking on the table '
'from the table list'),
'filter_select_enabled': _(
"Whether to populate the filter's dropdown in the explore "
"view's filter section with a list of distinct values fetched "
'from the backend on the fly'),
'is_sqllab_view': _(
"Whether the table was generated by the 'Visualize' flow "
'in SQL Lab'),
'template_params': _(
'A set of parameters that become available in the query using '
'Jinja templating syntax'),
'cache_timeout': _(
'Duration (in seconds) of the caching timeout for this table. '
'A timeout of 0 indicates that the cache never expires. '
'Note this defaults to the database timeout if undefined.'),
}
label_columns = {
'slices': _('Associated Charts'),
'link': _('Table'),
'changed_by_': _('Changed By'),
'database': _('Database'),
'database_name': _('Database'),
'changed_on_': _('Last Changed'),
'filter_select_enabled': _('Enable Filter Select'),
'schema': _('Schema'),
'default_endpoint': _('Default Endpoint'),
'offset': _('Offset'),
'cache_timeout': _('Cache Timeout'),
'table_name': _('Table Name'),
'fetch_values_predicate': _('Fetch Values Predicate'),
'owners': _('Owners'),
'main_dttm_col': _('Main Datetime Column'),
'description': _('Description'),
'is_sqllab_view': _('SQL Lab View'),
'template_params': _('Template parameters'),
'modified': _('Modified'),
}
def pre_add(self, table):
with db.session.no_autoflush:
table_query = db.session.query(models.SqlaTable).filter(
models.SqlaTable.table_name == table.table_name,
models.SqlaTable.schema == table.schema,
models.SqlaTable.database_id == table.database.id)
if db.session.query(table_query.exists()).scalar():
raise Exception(
get_datasource_exist_error_msg(table.full_name))
# Fail before adding if the table can't be found
try:
table.get_sqla_table_object()
except Exception:
raise Exception(_(
'Table [{}] could not be found, '
'please double check your '
'database connection, schema, and '
'table name').format(table.name))
def post_add(self, table, flash_message=True):
table.fetch_metadata()
security_manager.merge_perm('datasource_access', table.get_perm())
if table.schema:
security_manager.merge_perm('schema_access', table.schema_perm)
if flash_message:
flash(_(
'The table was created. '
'As part of this two-phase configuration '
'process, you should now click the edit button by '
'the new table to configure it.'), 'info')
def post_update(self, table):
self.post_add(table, flash_message=False)
def _delete(self, pk):
DeleteMixin._delete(self, pk)
@expose('/edit/<pk>', methods=['GET', 'POST'])
@has_access
def edit(self, pk):
"""Simple hack to redirect to explore view after saving"""
resp = super(TableModelView, self).edit(pk)
if isinstance(resp, basestring):
return resp
return redirect('/superset/explore/table/{}/'.format(pk))
@action(
'refresh',
__('Refresh Metadata'),
__('Refresh column metadata'),
'fa-refresh')
def refresh(self, tables):
if not isinstance(tables, list):
tables = [tables]
successes = []
failures = []
for t in tables:
try:
t.fetch_metadata()
successes.append(t)
except Exception:
failures.append(t)
if len(successes) > 0:
success_msg = _(
'Metadata refreshed for the following table(s): %(tables)s',
tables=', '.join([t.table_name for t in successes]))
flash(success_msg, 'info')
if len(failures) > 0:
failure_msg = _(
'Unable to retrieve metadata for the following table(s): %(tables)s',
tables=', '.join([t.table_name for t in failures]))
flash(failure_msg, 'danger')
return redirect('/tablemodelview/list/')
def run(self) -> None:
engine = self._properties["engine"]
engine_specs = get_engine_specs()
if engine in BYPASS_VALIDATION_ENGINES:
# Skip engines that are only validated onCreate
return
if engine not in engine_specs:
raise InvalidEngineError(
SupersetError(
message=__(
'Engine "%(engine)s" is not a valid engine.',
engine=engine,
),
error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR,
level=ErrorLevel.ERROR,
extra={
"allowed": list(engine_specs),
"provided": engine
},
), )
engine_spec = engine_specs[engine]
if not hasattr(engine_spec, "parameters_schema"):
raise InvalidEngineError(
SupersetError(
message=__(
'Engine "%(engine)s" cannot be configured through parameters.',
engine=engine,
),
error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR,
level=ErrorLevel.ERROR,
extra={
"allowed": [
name for name, engine_spec in engine_specs.items()
if issubclass(engine_spec, BasicParametersMixin)
],
"provided":
engine,
},
), )
# perform initial validation
errors = engine_spec.validate_parameters( # type: ignore
self._properties.get("parameters", {}))
if errors:
event_logger.log_with_context(action="validation_error",
engine=engine)
raise InvalidParametersError(errors)
serialized_encrypted_extra = self._properties.get(
"encrypted_extra", "{}")
try:
encrypted_extra = json.loads(serialized_encrypted_extra)
except json.decoder.JSONDecodeError:
encrypted_extra = {}
# try to connect
sqlalchemy_uri = engine_spec.build_sqlalchemy_uri( # type: ignore
self._properties.get("parameters"),
encrypted_extra,
)
if self._model and sqlalchemy_uri == self._model.safe_sqlalchemy_uri():
sqlalchemy_uri = self._model.sqlalchemy_uri_decrypted
database = DatabaseDAO.build_db_for_connection_test(
server_cert=self._properties.get("server_cert", ""),
extra=self._properties.get("extra", "{}"),
impersonate_user=self._properties.get("impersonate_user", False),
encrypted_extra=serialized_encrypted_extra,
)
database.set_sqlalchemy_uri(sqlalchemy_uri)
database.db_engine_spec.mutate_db_for_connection_test(database)
username = self._actor.username if self._actor is not None else None
engine = database.get_sqla_engine(user_name=username)
try:
with closing(engine.raw_connection()) as conn:
alive = engine.dialect.do_ping(conn)
except Exception as ex:
url = make_url_safe(sqlalchemy_uri)
context = {
"hostname": url.host,
"password": url.password,
"port": url.port,
"username": url.username,
"database": url.database,
}
errors = database.db_engine_spec.extract_errors(ex, context)
raise DatabaseTestConnectionFailedError(errors) from ex
if not alive:
raise DatabaseOfflineError(
SupersetError(
message=__("Database is offline."),
error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR,
level=ErrorLevel.ERROR,
), )
class QueryView(RookModelView):
datamodel = SQLAInterface(Query)
list_columns = ['user', 'database', 'status', 'start_time', 'end_time']
label_columns = {
'user': _('User'),
'database': _('Database'),
'status': _('Status'),
'start_time': _('Start Time'),
'end_time': _('End Time'),
}
appbuilder.add_view(QueryView,
'Queries',
label=__('Queries'),
category='Manage',
category_label=__('Manage'),
icon='fa-search')
class SavedQueryView(RookModelView, DeleteMixin):
datamodel = SQLAInterface(SavedQuery)
list_title = _('List Saved Query')
show_title = _('Show Saved Query')
add_title = _('Add Saved Query')
edit_title = _('Edit Saved Query')
list_columns = [
'label', 'user', 'database', 'schema', 'description', 'modified',
def run(self) -> Optional[Dict[str, Any]]:
initial_form_data = {}
if self._permalink_key is not None:
command = GetExplorePermalinkCommand(self._permalink_key)
permalink_value = command.run()
if not permalink_value:
raise ExplorePermalinkGetFailedError()
state = permalink_value["state"]
initial_form_data = state["formData"]
url_params = state.get("urlParams")
if url_params:
initial_form_data["url_params"] = dict(url_params)
elif self._form_data_key:
parameters = FormDataCommandParameters(key=self._form_data_key)
value = GetFormDataCommand(parameters).run()
initial_form_data = json.loads(value) if value else {}
message = None
if not initial_form_data:
if self._slice_id:
initial_form_data["slice_id"] = self._slice_id
if self._form_data_key:
message = _(
"Form data not found in cache, reverting to chart metadata."
)
elif self._dataset_id:
initial_form_data[
"datasource"
] = f"{self._dataset_id}__{self._dataset_type}"
if self._form_data_key:
message = _(
"Form data not found in cache, reverting to dataset metadata."
)
form_data, slc = get_form_data(
use_slice_data=True, initial_form_data=initial_form_data
)
try:
self._dataset_id, self._dataset_type = get_datasource_info(
self._dataset_id, self._dataset_type, form_data
)
except SupersetException:
self._dataset_id = None
# fallback unkonw datasource to table type
self._dataset_type = SqlaTable.type
dataset: Optional[BaseDatasource] = None
if self._dataset_id is not None:
try:
dataset = DatasourceDAO.get_datasource(
db.session, cast(str, self._dataset_type), self._dataset_id
)
except DatasetNotFoundError:
pass
dataset_name = dataset.name if dataset else _("[Missing Dataset]")
if dataset:
if app.config["ENABLE_ACCESS_REQUEST"] and (
not security_manager.can_access_datasource(dataset)
):
message = __(security_manager.get_datasource_access_error_msg(dataset))
raise DatasetAccessDeniedError(
message=message,
dataset_type=self._dataset_type,
dataset_id=self._dataset_id,
)
viz_type = form_data.get("viz_type")
if not viz_type and dataset and dataset.default_endpoint:
raise WrongEndpointError(redirect=dataset.default_endpoint)
form_data["datasource"] = (
str(self._dataset_id) + "__" + cast(str, self._dataset_type)
)
# On explore, merge legacy and extra filters into the form data
utils.convert_legacy_filters_into_adhoc(form_data)
utils.merge_extra_filters(form_data)
dummy_dataset_data: Dict[str, Any] = {
"type": self._dataset_type,
"name": dataset_name,
"columns": [],
"metrics": [],
"database": {"id": 0, "backend": ""},
}
try:
dataset_data = dataset.data if dataset else dummy_dataset_data
except (SupersetException, SQLAlchemyError):
dataset_data = dummy_dataset_data
return {
"dataset": sanitize_datasource_data(dataset_data),
"form_data": form_data,
"slice": slc.data if slc else None,
"message": message,
}
class TableModelView(DatasourceModelView, DeleteMixin, YamlExportMixin):
datamodel = SQLAInterface(models.SqlaTable)
include_route_methods = RouteMethod.CRUD_SET
list_title = _("Tables")
show_title = _("Show Table")
add_title = _("Import a table definition")
edit_title = _("Edit Table")
list_columns = ["link", "database_name", "changed_by_", "modified"]
order_columns = ["modified"]
add_columns = ["database", "schema", "table_name"]
edit_columns = [
"table_name",
"sql",
"filter_select_enabled",
"fetch_values_predicate",
"database",
"schema",
"description",
"owners",
"main_dttm_col",
"default_endpoint",
"offset",
"cache_timeout",
"is_sqllab_view",
"template_params",
]
base_filters = [["id", DatasourceFilter, lambda: []]]
show_columns = edit_columns + ["perm", "slices"]
related_views = [
TableColumnInlineView,
SqlMetricInlineView,
RowLevelSecurityFiltersModelView,
]
base_order = ("changed_on", "desc")
search_columns = ("database", "schema", "table_name", "owners",
"is_sqllab_view")
description_columns = {
"slices":
_("The list of charts associated with this table. By "
"altering this datasource, you may change how these associated "
"charts behave. "
"Also note that charts need to point to a datasource, so "
"this form will fail at saving if removing charts from a "
"datasource. If you want to change the datasource for a chart, "
"overwrite the chart from the 'explore view'"),
"offset":
_("Timezone offset (in hours) for this datasource"),
"table_name":
_("Name of the table that exists in the source database"),
"schema":
_("Schema, as used only in some databases like Postgres, Redshift "
"and DB2"),
"description":
Markup(
'Supports <a href="https://daringfireball.net/projects/markdown/">'
"markdown</a>"),
"sql":
_("This fields acts a Superset view, meaning that Superset will "
"run a query against this string as a subquery."),
"fetch_values_predicate":
_("Predicate applied when fetching distinct value to "
"populate the filter control component. Supports "
"jinja template syntax. Applies only when "
"`Enable Filter Select` is on."),
"default_endpoint":
_("Redirects to this endpoint when clicking on the table "
"from the table list"),
"filter_select_enabled":
_("Whether to populate the filter's dropdown in the explore "
"view's filter section with a list of distinct values fetched "
"from the backend on the fly"),
"is_sqllab_view":
_("Whether the table was generated by the 'Visualize' flow "
"in SQL Lab"),
"template_params":
_("A set of parameters that become available in the query using "
"Jinja templating syntax"),
"cache_timeout":
_("Duration (in seconds) of the caching timeout for this table. "
"A timeout of 0 indicates that the cache never expires. "
"Note this defaults to the database timeout if undefined."),
}
label_columns = {
"slices": _("Associated Charts"),
"link": _("Table"),
"changed_by_": _("Changed By"),
"database": _("Database"),
"database_name": _("Database"),
"changed_on_": _("Last Changed"),
"filter_select_enabled": _("Enable Filter Select"),
"schema": _("Schema"),
"default_endpoint": _("Default Endpoint"),
"offset": _("Offset"),
"cache_timeout": _("Cache Timeout"),
"table_name": _("Table Name"),
"fetch_values_predicate": _("Fetch Values Predicate"),
"owners": _("Owners"),
"main_dttm_col": _("Main Datetime Column"),
"description": _("Description"),
"is_sqllab_view": _("SQL Lab View"),
"template_params": _("Template parameters"),
"modified": _("Modified"),
}
edit_form_extra_fields = {
"database":
QuerySelectField(
"Database",
query_factory=lambda: db.session().query(models.Database),
widget=Select2Widget(extra_classes="readonly"),
)
}
def pre_add(self, table: "TableModelView") -> None:
validate_sqlatable(table)
def post_add(self,
table: "TableModelView",
flash_message: bool = True) -> None:
table.fetch_metadata()
create_table_permissions(table)
if flash_message:
flash(
_("The table was created. "
"As part of this two-phase configuration "
"process, you should now click the edit button by "
"the new table to configure it."),
"info",
)
def post_update(self, table: "TableModelView") -> None:
self.post_add(table, flash_message=False)
def _delete(self, pk: int) -> None:
DeleteMixin._delete(self, pk)
@expose("/edit/<pk>", methods=["GET", "POST"])
@has_access
def edit(self, pk: int) -> FlaskResponse:
"""Simple hack to redirect to explore view after saving"""
resp = super(TableModelView, self).edit(pk)
if isinstance(resp, str):
return resp
return redirect("/superset/explore/table/{}/".format(pk))
@action("refresh", __("Refresh Metadata"), __("Refresh column metadata"),
"fa-refresh")
def refresh(
self, tables: Union["TableModelView",
List["TableModelView"]]) -> FlaskResponse:
if not isinstance(tables, list):
tables = [tables]
successes = []
failures = []
for t in tables:
try:
t.fetch_metadata()
successes.append(t)
except Exception:
failures.append(t)
if len(successes) > 0:
success_msg = _(
"Metadata refreshed for the following table(s): %(tables)s",
tables=", ".join([t.table_name for t in successes]),
)
flash(success_msg, "info")
if len(failures) > 0:
failure_msg = _(
"Unable to retrieve metadata for the following table(s): %(tables)s",
tables=", ".join([t.table_name for t in failures]),
)
flash(failure_msg, "danger")
return redirect("/tablemodelview/list/")
@expose("/list/")
@has_access
def list(self) -> FlaskResponse:
if not app.config["ENABLE_REACT_CRUD_VIEWS"]:
return super().list()
return super().render_app_template()
logger = logging.getLogger()
class TimeGrain(NamedTuple): # pylint: disable=too-few-public-methods
name: str # TODO: redundant field, remove
label: str
function: str
duration: Optional[str]
QueryStatus = utils.QueryStatus
config = app.config
builtin_time_grains: Dict[Optional[str], str] = {
None: __("Original value"),
"PT1S": __("Second"),
"PT1M": __("Minute"),
"PT5M": __("5 minute"),
"PT10M": __("10 minute"),
"PT15M": __("15 minute"),
"PT0.5H": __("Half hour"),
"PT1H": __("Hour"),
"P1D": __("Day"),
"P1W": __("Week"),
"P1M": __("Month"),
"P0.25Y": __("Quarter"),
"P1Y": __("Year"),
"1969-12-28T00:00:00Z/P1W": __("Week starting sunday"),
"1969-12-29T00:00:00Z/P1W": __("Week starting monday"),
"P1W/1970-01-03T00:00:00Z": __("Week ending saturday"),
'broker_endpoint': _("Broker Endpoint"),
}
def pre_add(self, cluster):
security.merge_perm(sm, 'database_access', cluster.perm)
def pre_update(self, cluster):
self.pre_add(cluster)
def _delete(self, pk):
DeleteMixin._delete(self, pk)
appbuilder.add_view(
DruidClusterModelView,
name="Druid Clusters",
label=__("Druid Clusters"),
icon="fa-cubes",
category="Sources",
category_label=__("Sources"),
category_icon='fa-database',)
class DruidDatasourceModelView(DatasourceModelView, DeleteMixin): # noqa
datamodel = SQLAInterface(models.DruidDatasource)
list_title = _('List Druid Datasource')
show_title = _('Show Druid Datasource')
add_title = _('Add Druid Datasource')
edit_title = _('Edit Druid Datasource')
list_widget = ListWidgetWithCheckboxes
obj.end_dttm = obj.start_dttm
elif obj.end_dttm < obj.start_dttm:
raise Exception(
'Annotation end time must be no earlier than start time.')
def pre_update(self, obj):
self.pre_add(obj)
class AnnotationLayerModelView(SupersetModelView, DeleteMixin):
datamodel = SQLAInterface(AnnotationLayer)
list_columns = ['id', 'name']
edit_columns = ['name', 'descr']
add_columns = edit_columns
appbuilder.add_view(AnnotationLayerModelView,
'Annotation Layers',
label=__('Annotation Layers'),
icon='fa-comment',
category='Manage',
category_label=__('Manage'),
category_icon='')
appbuilder.add_view(AnnotationModelView,
'Annotations',
label=__('Annotations'),
icon='fa-comments',
category='Manage',
category_label=__('Manage'),
category_icon='')
class Job_Template_ModelView_Base():
datamodel = SQLAInterface(Job_Template)
label_title = '任务模板'
check_redirect_list_url = '/job_template_modelview/list/?_flt_2_name='
help_url = conf.get('HELP_URL', {}).get(datamodel.obj.__tablename__,
'') if datamodel else ''
list_columns = ['project', 'name_title', 'version', 'creator', 'modified']
show_columns = [
'project', 'name', 'version', 'describe', 'images_url', 'workdir',
'entrypoint', 'args_html', 'demo_html', 'env', 'hostAliases',
'privileged', 'expand_html'
]
add_columns = [
'project', 'images', 'name', 'version', 'describe', 'workdir',
'entrypoint', 'volume_mount', 'job_args_definition', 'args', 'env',
'hostAliases', 'privileged', 'accounts', 'demo', 'expand'
]
edit_columns = add_columns
base_filters = [["id", Job_Tempalte_Filter, lambda: []]] # 设置权限过滤器
base_order = ('id', 'desc')
order_columns = ['id']
add_form_query_rel_fields = {
"images": [["name", Images_Filter, None]],
"project": [["name", Project_Filter, 'job-template']],
}
version_list = [[version, version] for version in ['Alpha', 'Release']]
edit_form_query_rel_fields = add_form_query_rel_fields
add_form_extra_fields = {
"name":
StringField(
_(datamodel.obj.lab('name')),
description='英文名(字母、数字、- 组成)，最长50个字符',
widget=BS3TextFieldWidget(
), # 传给widget函数的是外层的field对象，以及widget函数的参数
validators=[Regexp("^[a-z][a-z0-9\-]*[a-z0-9]$"),
Length(1, 54)]),
"version":
SelectField(_(datamodel.obj.lab('version')),
description="job模板的版本，release版本的模板才能被所有用户看到",
widget=Select2Widget(),
choices=version_list),
"volume_mount":
StringField(
_(datamodel.obj.lab('volume_mount')),
description='使用该模板的task，会在保存时，自动添加该挂载',
widget=BS3TextFieldWidget(
), # 传给widget函数的是外层的field对象，以及widget函数的参数
),
"workdir":
StringField(
_(datamodel.obj.lab('workdir')),
description='工作目录，不填写将直接使用镜像默认的工作目录',
widget=BS3TextFieldWidget(
), # 传给widget函数的是外层的field对象，以及widget函数的参数
),
"entrypoint":
StringField(
_(datamodel.obj.lab('entrypoint')),
description='镜像的入口命令，直接写成单行字符串，例如python xx.py，无需添加[]',
widget=BS3TextFieldWidget(
), # 传给widget函数的是外层的field对象，以及widget函数的参数
),
"args":
StringField(
_(datamodel.obj.lab('args')),
description='使用job模板，task需要填写的参数，需要按Job Args Definition的标准写入',
widget=MyBS3TextAreaFieldWidget(
rows=10), # 传给widget函数的是外层的field对象，以及widget函数的参数
),
"env":
StringField(
_(datamodel.obj.lab('env')),
description=
'使用模板的task自动添加的环境变量，支持模板变量。书写格式:每行一个环境变量env_key=env_value',
widget=MyBS3TextAreaFieldWidget(
rows=3), # 传给widget函数的是外层的field对象，以及widget函数的参数
),
"hostAliases":
StringField(
_(datamodel.obj.lab('hostAliases')),
description=
'添加到容器内的host映射。书写格式:每行一个dns解析记录，ip host1 host2，示例：1.1.1.1 example1.oa.com example2.oa.com',
widget=MyBS3TextAreaFieldWidget(
rows=3), # 传给widget函数的是外层的field对象，以及widget函数的参数
),
"demo":
StringField(
_(datamodel.obj.lab('demo')),
description='填写demo',
widget=MyBS3TextAreaFieldWidget(
rows=10), # 传给widget函数的是外层的field对象，以及widget函数的参数
),
"job_args_definition":
StringField(
_(datamodel.obj.lab('job_args_definition')),
description='使用job模板参数的标准填写方式',
widget=MyCodeArea(code=core.job_template_args_definition()
), # 传给widget函数的是外层的field对象，以及widget函数的参数
),
"privileged":
BooleanField(_(datamodel.obj.lab('privileged')),
description='是否启动超级权限'),
"expand":
StringField(
_(datamodel.obj.lab('expand')),
description='json格式的扩展字段，支持 index:$模板展示顺序号，help_url:$帮助文档地址',
widget=MyBS3TextAreaFieldWidget(
rows=3), # 传给widget函数的是外层的field对象，以及widget函数的参数
),
}
edit_form_extra_fields = add_form_extra_fields
# 校验是否是json
# @pysnooper.snoop(watch_explode=('job_args'))
def pre_add(self, item):
if not item.env:
item.env = ''
envs = item.env.strip().split('\n')
envs = [env.strip() for env in envs if env.strip() and '=' in env]
item.env = '\n'.join(envs)
if not item.args:
item.args = '{}'
item.args = core.validate_job_args(item)
if not item.expand or not item.expand.strip():
item.expand = '{}'
core.validate_json(item.expand)
item.expand = json.dumps(json.loads(item.expand),
indent=4,
ensure_ascii=False)
if not item.demo or not item.demo.strip():
item.demo = '{}'
core.validate_json(item.demo)
if item.hostAliases:
# if not item.images.entrypoint:
# raise MyappException('images entrypoint not exist')
all_host = {}
all_rows = re.split('\r|\n', item.hostAliases)
all_rows = [
all_row.strip() for all_row in all_rows if all_row.strip()
]
for row in all_rows:
hosts = row.split(' ')
hosts = [host for host in hosts if host]
if len(hosts) > 1:
if hosts[0] in all_host:
all_host[hosts[0]] = all_host[hosts[0]] + hosts[1:]
else:
all_host[hosts[0]] = hosts[1:]
hostAliases = ''
for ip in all_host:
hostAliases += ip + " " + " ".join(all_host[ip])
hostAliases += '\n'
item.hostAliases = hostAliases.strip()
task_args = json.loads(item.demo)
job_args = json.loads(item.args)
item.demo = json.dumps(core.validate_task_args(task_args, job_args),
indent=4,
ensure_ascii=False)
# 检测是否具有编辑权限，只有creator和admin可以编辑
def check_edit_permission(self, item):
user_roles = [role.name.lower() for role in list(get_user_roles())]
if "admin" in user_roles:
return True
if g.user and g.user.username and hasattr(item, 'created_by'):
if g.user.username == item.created_by.username:
return True
flash('just creator can edit/delete ', 'warning')
return False
def pre_update(self, item):
self.pre_add(item)
# @pysnooper.snoop()
def post_list(self, items):
def sort_expand_index(items, dbsession):
all = {0: []}
for item in items:
try:
if item.expand:
index = float(json.loads(item.expand).get(
'index', 0)) + float(
json.loads(item.project.expand).get(
'index', 0)) * 1000
if index:
if index in all:
all[index].append(item)
else:
all[index] = [item]
else:
all[0].append(item)
else:
all[0].append(item)
except Exception as e:
print(e)
back = []
for index in sorted(all):
back.extend(all[index])
# 当有小数的时候自动转正
# if float(index)!=int(index):
# pass
return back
return sort_expand_index(items, db.session)
@expose("/run", methods=["POST"])
def run(self):
request_data = request.json
job_template_id = request_data.get('job_template_id', '')
job_template_name = request_data.get('job_template_name', '')
run_id = request_data.get('run_id', '').replace('_', '-')
resource_memory = request_data.get('resource_memory', '')
resource_cpu = request_data.get('resource_cpu', '')
task_args = request_data.get('args', '')
if (not job_template_id
and not job_template_name) or not run_id or task_args == '':
response = make_response("输入参数不齐全")
response.status_code = 400
return response
job_template = None
if job_template_id:
job_template = db.session.query(Job_Template).filter_by(
id=int(job_template_id)).first()
elif job_template_name:
job_template = db.session.query(Job_Template).filter_by(
name=job_template_name).first()
if not job_template:
response = make_response("no job template exist")
response.status_code = 400
return response
from myapp.utils.py.py_k8s import K8s
k8s = K8s()
namespace = conf.get('PIPELINE_NAMESPACE')
pod_name = "venus-" + run_id.replace('_', '-')
pod_name = pod_name.lower()[:60].strip('-')
pod = k8s.get_pods(namespace=namespace, pod_name=pod_name)
# print(pod)
if pod:
pod = pod[0]
# 有历史，直接删除
if pod:
k8s.delete_pods(namespace=namespace, pod_name=pod_name)
time.sleep(2)
pod = None
# 没有历史或者没有运行态，直接创建
if not pod:
args = []
job_template_args = json.loads(
job_template.args) if job_template.args else {}
for arg_name in task_args:
arg_type = ''
for group in job_template_args:
for template_arg in job_template_args[group]:
if template_arg == arg_name:
arg_type = job_template_args[group][
template_arg].get('type', '')
arg_value = task_args[arg_name]
if arg_value:
args.append(arg_name)
if arg_type == 'json':
args.append(json.dumps(arg_value))
else:
args.append(arg_value)
# command = ['sh', '-c','sleep 7200']
volume_mount = 'kubeflow-cfs-workspace(pvc):/mnt,kubeflow-cfs-archives(pvc):/archives'
env = job_template.env + "\n"
env += 'KFJ_TASK_ID=0\n'
env += 'KFJ_TASK_NAME=' + str('venus-' + run_id) + "\n"
env += 'KFJ_TASK_NODE_SELECTOR=cpu=true,train=true\n'
env += 'KFJ_TASK_VOLUME_MOUNT=' + str(volume_mount) + "\n"
env += 'KFJ_TASK_IMAGES=' + str(job_template.images) + "\n"
env += 'KFJ_TASK_RESOURCE_CPU=' + str(resource_cpu) + "\n"
env += 'KFJ_TASK_RESOURCE_MEMORY=' + str(resource_memory) + "\n"
env += 'KFJ_TASK_RESOURCE_GPU=0\n'
env += 'KFJ_PIPELINE_ID=0\n'
env += 'KFJ_RUN_ID=' + run_id + "\n"
env += 'KFJ_CREATOR=' + str(g.user.username) + "\n"
env += 'KFJ_RUNNER=' + str(g.user.username) + "\n"
env += 'KFJ_PIPELINE_NAME=venus\n'
env += 'KFJ_NAMESPACE=pipeline' + "\n"
def template_str(src_str):
rtemplate = Environment(
loader=BaseLoader,
undefined=DebugUndefined).from_string(src_str)
des_str = rtemplate.render(
creator=g.user.username,
datetime=datetime,
runner=g.user.username,
uuid=uuid,
pipeline_id='0',
pipeline_name='venus-task',
cluster_name=conf.get('ENVIRONMENT'))
return des_str
global_envs = json.loads(
template_str(
json.dumps(conf.get('GLOBAL_ENV', {}),
indent=4,
ensure_ascii=False)))
for global_env_key in global_envs:
env += global_env_key + '=' + global_envs[global_env_key] + "\n"
hostAliases = job_template.hostAliases + "\n" + conf.get(
'HOSTALIASES', '')
k8s.create_debug_pod(
namespace,
name=pod_name,
labels={'run-rtx': g.user.username},
command=None,
args=args,
volume_mount=volume_mount,
working_dir=None,
node_selector='cpu=true,train=true',
resource_cpu=resource_cpu,
resource_memory=resource_memory,
resource_gpu=0,
image_pull_policy=conf.get('IMAGE_PULL_POLICY', 'Always'),
image_pull_secrets=[job_template.images.repository.hubsecret],
image=job_template.images.name,
hostAliases=hostAliases,
env=env,
privileged=job_template.privileged,
accounts=job_template.accounts,
username=g.user.username)
try_num = 5
while (try_num > 0):
pod = k8s.get_pods(namespace=namespace, pod_name=pod_name)
# print(pod)
if pod:
break
try_num = try_num - 1
time.sleep(2)
if try_num == 0:
response = make_response("启动时间过长，一分钟后重试")
response.status_code = 400
return response
user_roles = [role.name.lower() for role in list(g.user.roles)]
if "admin" in user_roles:
pod_url = conf.get(
'K8S_DASHBOARD_CLUSTER'
) + "#/log/%s/%s/pod?namespace=%s&container=%s" % (
namespace, pod_name, namespace, pod_name)
else:
pod_url = conf.get(
'K8S_DASHBOARD_PIPELINE'
) + "#/log/%s/%s/pod?namespace=%s&container=%s" % (
namespace, pod_name, namespace, pod_name)
print(pod_url)
response = make_response("启动成功，日志地址: %s" % pod_url)
response.status_code = 200
return response
@expose("/listen", methods=["POST"])
def listen(self):
request_data = request.json
run_id = request_data.get('run_id', '').replace('_', '-')
if not run_id:
response = make_response("输入参数不齐全")
response.status_code = 400
return response
from myapp.utils.py.py_k8s import K8s
k8s = K8s()
namespace = conf.get('PIPELINE_NAMESPACE')
pod_name = "venus-" + run_id.replace('_', '-')
pod_name = pod_name.lower()[:60].strip('-')
pod = k8s.get_pods(namespace=namespace, pod_name=pod_name)
# print(pod)
if pod:
pod = pod[0]
if type(pod['start_time']) == datetime.datetime:
pod['start_time'] = pod['start_time'].strftime(
"%Y-%d-%m %H:%M:%S")
print(pod)
response = make_response(json.dumps(pod))
response.status_code = 200
return response
else:
response = make_response('no pod')
response.status_code = 400
return response
@action("copy",
__("Copy Job Template"),
confirmation=__('Copy Job Template'),
icon="fa-copy",
multiple=True,
single=False)
def copy(self, job_templates):
if not isinstance(job_templates, list):
job_templates = [job_templates]
try:
for job_template in job_templates:
new_job_template = job_template.clone()
new_job_template.name = new_job_template.name + "_copy_" + uuid.uuid4(
).hex[:4]
new_job_template.created_on = datetime.datetime.now()
new_job_template.changed_on = datetime.datetime.now()
db.session.add(new_job_template)
db.session.commit()
except InvalidRequestError:
db.session.rollback()
except Exception as e:
raise e
return redirect(request.referrer)
class SnowflakeEngineSpec(PostgresBaseEngineSpec):
engine = "snowflake"
engine_name = "Snowflake"
force_column_alias_quotes = True
max_column_name_length = 256
_time_grain_expressions = {
None: "{col}",
"PT1S": "DATE_TRUNC('SECOND', {col})",
"PT1M": "DATE_TRUNC('MINUTE', {col})",
"PT5M": "DATEADD(MINUTE, FLOOR(DATE_PART(MINUTE, {col}) / 5) * 5, \
DATE_TRUNC('HOUR', {col}))",
"PT10M": "DATEADD(MINUTE, FLOOR(DATE_PART(MINUTE, {col}) / 10) * 10, \
DATE_TRUNC('HOUR', {col}))",
"PT15M": "DATEADD(MINUTE, FLOOR(DATE_PART(MINUTE, {col}) / 15) * 15, \
DATE_TRUNC('HOUR', {col}))",
"PT0.5H": "DATEADD(MINUTE, FLOOR(DATE_PART(MINUTE, {col}) / 30) * 30, \
DATE_TRUNC('HOUR', {col}))",
"PT1H": "DATE_TRUNC('HOUR', {col})",
"P1D": "DATE_TRUNC('DAY', {col})",
"P1W": "DATE_TRUNC('WEEK', {col})",
"P1M": "DATE_TRUNC('MONTH', {col})",
"P0.25Y": "DATE_TRUNC('QUARTER', {col})",
"P1Y": "DATE_TRUNC('YEAR', {col})",
}
custom_errors: Dict[Pattern[str], Tuple[str, SupersetErrorType, Dict[
str, Any]]] = {
OBJECT_DOES_NOT_EXIST_REGEX: (
__("%(object)s does not exist in this database."),
SupersetErrorType.OBJECT_DOES_NOT_EXIST_ERROR,
{},
),
SYNTAX_ERROR_REGEX: (
__("Please check your query for syntax errors at or "
'near "%(syntax_error)s". Then, try running your query again.'
),
SupersetErrorType.SYNTAX_ERROR,
{},
),
}
@classmethod
def adjust_database_uri(cls,
uri: URL,
selected_schema: Optional[str] = None) -> None:
database = uri.database
if "/" in uri.database:
database = uri.database.split("/")[0]
if selected_schema:
selected_schema = parse.quote(selected_schema, safe="")
uri.database = database + "/" + selected_schema
@classmethod
def epoch_to_dttm(cls) -> str:
return "DATEADD(S, {col}, '1970-01-01')"
@classmethod
def epoch_ms_to_dttm(cls) -> str:
return "DATEADD(MS, {col}, '1970-01-01')"
@classmethod
def convert_dttm(cls, target_type: str, dttm: datetime) -> Optional[str]:
tt = target_type.upper()
if tt == utils.TemporalType.DATE:
return f"TO_DATE('{dttm.date().isoformat()}')"
if tt == utils.TemporalType.DATETIME:
return f"""CAST('{dttm.isoformat(timespec="microseconds")}' AS DATETIME)"""
if tt == utils.TemporalType.TIMESTAMP:
return f"""TO_TIMESTAMP('{dttm.isoformat(timespec="microseconds")}')"""
return None
@staticmethod
def mutate_db_for_connection_test(database: "Database") -> None:
"""
By default, snowflake doesn't validate if the user/role has access to the chosen
database.
:param database: instance to be mutated
"""
extra = json.loads(database.extra or "{}")
engine_params = extra.get("engine_params", {})
connect_args = engine_params.get("connect_args", {})
connect_args["validate_default_parameters"] = True
engine_params["connect_args"] = connect_args
extra["engine_params"] = engine_params
database.extra = json.dumps(extra)
@classmethod
def get_cancel_query_id(cls, cursor: Any, query: Query) -> Optional[str]:
"""
Get Snowflake session ID that will be used to cancel all other running
queries in the same session.
:param cursor: Cursor instance in which the query will be executed
:param query: Query instance
:return: Snowflake Session ID
"""
cursor.execute("SELECT CURRENT_SESSION()")
row = cursor.fetchone()
return row[0]
@classmethod
def cancel_query(cls, cursor: Any, query: Query,
cancel_query_id: str) -> bool:
"""
Cancel query in the underlying database.
:param cursor: New cursor instance to the db of the query
:param query: Query instance
:param cancel_query_id: Snowflake Session ID
:return: True if query cancelled successfully, False otherwise
"""
try:
cursor.execute(
f"SELECT SYSTEM$CANCEL_ALL_QUERIES({cancel_query_id})")
except Exception: # pylint: disable=broad-except
return False
return True
def execute_sql_statements( # pylint: disable=too-many-arguments, too-many-locals, too-many-statements, too-many-branches
query_id: int,
rendered_query: str,
return_results: bool,
store_results: bool,
user_name: Optional[str],
session: Session,
start_time: Optional[float],
expand_data: bool,
log_params: Optional[Dict[str, Any]],
) -> Optional[Dict[str, Any]]:
"""Executes the sql query returns the results."""
if store_results and start_time:
# only asynchronous queries
stats_logger.timing("sqllab.query.time_pending", now_as_float() - start_time)
query = get_query(query_id, session)
payload: Dict[str, Any] = dict(query_id=query_id)
database = query.database
db_engine_spec = database.db_engine_spec
db_engine_spec.patch()
if database.allow_run_async and not results_backend:
raise SupersetErrorException(
SupersetError(
message=__("Results backend is not configured."),
error_type=SupersetErrorType.RESULTS_BACKEND_NOT_CONFIGURED_ERROR,
level=ErrorLevel.ERROR,
)
)
# Breaking down into multiple statements
parsed_query = ParsedQuery(rendered_query, strip_comments=True)
if not db_engine_spec.run_multiple_statements_as_one:
statements = parsed_query.get_statements()
logger.info(
"Query %s: Executing %i statement(s)", str(query_id), len(statements)
)
else:
statements = [rendered_query]
logger.info("Query %s: Executing query as a single statement", str(query_id))
logger.info("Query %s: Set query to 'running'", str(query_id))
query.status = QueryStatus.RUNNING
query.start_running_time = now_as_float()
session.commit()
# Should we create a table or view from the select?
if (
query.select_as_cta
and query.ctas_method == CtasMethod.TABLE
and not parsed_query.is_valid_ctas()
):
raise SupersetErrorException(
SupersetError(
message=__(
"CTAS (create table as select) can only be run with a query where "
"the last statement is a SELECT. Please make sure your query has "
"a SELECT as its last statement. Then, try running your query "
"again."
),
error_type=SupersetErrorType.INVALID_CTAS_QUERY_ERROR,
level=ErrorLevel.ERROR,
)
)
if (
query.select_as_cta
and query.ctas_method == CtasMethod.VIEW
and not parsed_query.is_valid_cvas()
):
raise SupersetErrorException(
SupersetError(
message=__(
"CVAS (create view as select) can only be run with a query with "
"a single SELECT statement. Please make sure your query has only "
"a SELECT statement. Then, try running your query again."
),
error_type=SupersetErrorType.INVALID_CVAS_QUERY_ERROR,
level=ErrorLevel.ERROR,
)
)
engine = database.get_sqla_engine(
schema=query.schema,
nullpool=True,
user_name=user_name,
source=QuerySource.SQL_LAB,
)
# Sharing a single connection and cursor across the
# execution of all statements (if many)
with closing(engine.raw_connection()) as conn:
# closing the connection closes the cursor as well
cursor = conn.cursor()
cancel_query_id = db_engine_spec.get_cancel_query_id(cursor, query)
if cancel_query_id is not None:
query.set_extra_json_key(cancel_query_key, cancel_query_id)
session.commit()
statement_count = len(statements)
for i, statement in enumerate(statements):
# Check if stopped
session.refresh(query)
if query.status == QueryStatus.STOPPED:
payload.update({"status": query.status})
return payload
# For CTAS we create the table only on the last statement
apply_ctas = query.select_as_cta and (
query.ctas_method == CtasMethod.VIEW
or (query.ctas_method == CtasMethod.TABLE and i == len(statements) - 1)
)
# Run statement
msg = f"Running statement {i+1} out of {statement_count}"
logger.info("Query %s: %s", str(query_id), msg)
query.set_extra_json_key("progress", msg)
session.commit()
try:
result_set = execute_sql_statement(
statement,
query,
user_name,
session,
cursor,
log_params,
apply_ctas,
)
except SqlLabQueryStoppedException:
payload.update({"status": QueryStatus.STOPPED})
return payload
except Exception as ex: # pylint: disable=broad-except
msg = str(ex)
prefix_message = (
f"[Statement {i+1} out of {statement_count}]"
if statement_count > 1
else ""
)
payload = handle_query_error(
ex, query, session, payload, prefix_message
)
return payload
# Commit the connection so CTA queries will create the table.
conn.commit()
# Success, updating the query entry in database
query.rows = result_set.size
query.progress = 100
query.set_extra_json_key("progress", None)
if query.select_as_cta:
query.select_sql = database.select_star(
query.tmp_table_name,
schema=query.tmp_schema_name,
limit=query.limit,
show_cols=False,
latest_partition=False,
)
query.end_time = now_as_float()
use_arrow_data = store_results and cast(bool, results_backend_use_msgpack)
data, selected_columns, all_columns, expanded_columns = _serialize_and_expand_data(
result_set, db_engine_spec, use_arrow_data, expand_data
)
# TODO: data should be saved separately from metadata (likely in Parquet)
payload.update(
{
"status": QueryStatus.SUCCESS,
"data": data,
"columns": all_columns,
"selected_columns": selected_columns,
"expanded_columns": expanded_columns,
"query": query.to_dict(),
}
)
payload["query"]["state"] = QueryStatus.SUCCESS
if store_results and results_backend:
key = str(uuid.uuid4())
logger.info(
"Query %s: Storing results in results backend, key: %s", str(query_id), key
)
with stats_timing("sqllab.query.results_backend_write", stats_logger):
with stats_timing(
"sqllab.query.results_backend_write_serialization", stats_logger
):
serialized_payload = _serialize_payload(
payload, cast(bool, results_backend_use_msgpack)
)
cache_timeout = database.cache_timeout
if cache_timeout is None:
cache_timeout = config["CACHE_DEFAULT_TIMEOUT"]
compressed = zlib_compress(serialized_payload)
logger.debug(
"*** serialized payload size: %i", getsizeof(serialized_payload)
)
logger.debug("*** compressed payload size: %i", getsizeof(compressed))
results_backend.set(key, compressed, cache_timeout)
query.results_key = key
query.status = QueryStatus.SUCCESS
session.commit()
if return_results:
# since we're returning results we need to create non-arrow data
if use_arrow_data:
(
data,
selected_columns,
all_columns,
expanded_columns,
) = _serialize_and_expand_data(
result_set, db_engine_spec, False, expand_data
)
payload.update(
{
"data": data,
"columns": all_columns,
"selected_columns": selected_columns,
"expanded_columns": expanded_columns,
}
)
return payload
return None
class BigQueryEngineSpec(BaseEngineSpec):
"""Engine spec for Google's BigQuery
As contributed by @mxmzdlv on issue #945"""
engine = "bigquery"
engine_name = "Google BigQuery"
max_column_name_length = 128
parameters_schema = BigQueryParametersSchema()
default_driver = "bigquery"
sqlalchemy_uri_placeholder = "bigquery://{project_id}"
# BigQuery doesn't maintain context when running multiple statements in the
# same cursor, so we need to run all statements at once
run_multiple_statements_as_one = True
allows_hidden_cc_in_orderby = True
"""
https://www.python.org/dev/peps/pep-0249/#arraysize
raw_connections bypass the pybigquery query execution context and deal with
raw dbapi connection directly.
If this value is not set, the default value is set to 1, as described here,
https://googlecloudplatform.github.io/google-cloud-python/latest/_modules/google/cloud/bigquery/dbapi/cursor.html#Cursor
The default value of 5000 is derived from the pybigquery.
https://github.com/mxmzdlv/pybigquery/blob/d214bb089ca0807ca9aaa6ce4d5a01172d40264e/pybigquery/sqlalchemy_bigquery.py#L102
"""
arraysize = 5000
_date_trunc_functions = {
"DATE": "DATE_TRUNC",
"DATETIME": "DATETIME_TRUNC",
"TIME": "TIME_TRUNC",
"TIMESTAMP": "TIMESTAMP_TRUNC",
}
_time_grain_expressions = {
None:
"{col}",
"PT1S":
"CAST(TIMESTAMP_SECONDS("
"UNIX_SECONDS(CAST({col} AS TIMESTAMP))"
") AS {type})",
"PT1M":
"CAST(TIMESTAMP_SECONDS("
"60 * DIV(UNIX_SECONDS(CAST({col} AS TIMESTAMP)), 60)"
") AS {type})",
"PT5M":
"CAST(TIMESTAMP_SECONDS("
"5*60 * DIV(UNIX_SECONDS(CAST({col} AS TIMESTAMP)), 5*60)"
") AS {type})",
"PT10M":
"CAST(TIMESTAMP_SECONDS("
"10*60 * DIV(UNIX_SECONDS(CAST({col} AS TIMESTAMP)), 10*60)"
") AS {type})",
"PT15M":
"CAST(TIMESTAMP_SECONDS("
"15*60 * DIV(UNIX_SECONDS(CAST({col} AS TIMESTAMP)), 15*60)"
") AS {type})",
"PT30M":
"CAST(TIMESTAMP_SECONDS("
"30*60 * DIV(UNIX_SECONDS(CAST({col} AS TIMESTAMP)), 30*60)"
") AS {type})",
"PT1H":
"{func}({col}, HOUR)",
"P1D":
"{func}({col}, DAY)",
"P1W":
"{func}({col}, WEEK)",
"1969-12-29T00:00:00Z/P1W":
"{func}({col}, ISOWEEK)",
"P1M":
"{func}({col}, MONTH)",
"P3M":
"{func}({col}, QUARTER)",
"P1Y":
"{func}({col}, YEAR)",
}
custom_errors: Dict[Pattern[str], Tuple[str, SupersetErrorType, Dict[
str, Any]]] = {
CONNECTION_DATABASE_PERMISSIONS_REGEX: (
__("We were unable to connect to your database. Please "
"confirm that your service account has the Viewer "
"and Job User roles on the project."),
SupersetErrorType.CONNECTION_DATABASE_PERMISSIONS_ERROR,
{},
),
TABLE_DOES_NOT_EXIST_REGEX: (
__(
'The table "%(table)s" does not exist. '
"A valid table must be used to run this query.", ),
SupersetErrorType.TABLE_DOES_NOT_EXIST_ERROR,
{},
),
COLUMN_DOES_NOT_EXIST_REGEX: (
__('We can\'t seem to resolve column "%(column)s" at line %(location)s.'
),
SupersetErrorType.COLUMN_DOES_NOT_EXIST_ERROR,
{},
),
SCHEMA_DOES_NOT_EXIST_REGEX: (
__('The schema "%(schema)s" does not exist. '
"A valid schema must be used to run this query."),
SupersetErrorType.SCHEMA_DOES_NOT_EXIST_ERROR,
{},
),
SYNTAX_ERROR_REGEX: (
__("Please check your query for syntax errors at or near "
'"%(syntax_error)s". Then, try running your query again.'),
SupersetErrorType.SYNTAX_ERROR,
{},
),
}
@classmethod
def convert_dttm(
cls,
target_type: str,
dttm: datetime,
db_extra: Optional[Dict[str, Any]] = None) -> Optional[str]:
tt = target_type.upper()
if tt == utils.TemporalType.DATE:
return f"CAST('{dttm.date().isoformat()}' AS DATE)"
if tt == utils.TemporalType.DATETIME:
return f"""CAST('{dttm.isoformat(timespec="microseconds")}' AS DATETIME)"""
if tt == utils.TemporalType.TIME:
return f"""CAST('{dttm.strftime("%H:%M:%S.%f")}' AS TIME)"""
if tt == utils.TemporalType.TIMESTAMP:
return f"""CAST('{dttm.isoformat(timespec="microseconds")}' AS TIMESTAMP)"""
return None
@classmethod
def fetch_data(cls,
cursor: Any,
limit: Optional[int] = None) -> List[Tuple[Any, ...]]:
data = super().fetch_data(cursor, limit)
# Support type BigQuery Row, introduced here PR #4071
# google.cloud.bigquery.table.Row
if data and type(data[0]).__name__ == "Row":
data = [r.values() for r in data] # type: ignore
return data
@staticmethod
def _mutate_label(label: str) -> str:
"""
BigQuery field_name should start with a letter or underscore and contain only
alphanumeric characters. Labels that start with a number are prefixed with an
underscore. Any unsupported characters are replaced with underscores and an
md5 hash is added to the end of the label to avoid possible collisions.
:param label: Expected expression label
:return: Conditionally mutated label
"""
label_hashed = "_" + md5_sha_from_str(label)
# if label starts with number, add underscore as first character
label_mutated = "_" + label if re.match(r"^\d", label) else label
# replace non-alphanumeric characters with underscores
label_mutated = re.sub(r"[^\w]+", "_", label_mutated)
if label_mutated != label:
# add first 5 chars from md5 hash to label to avoid possible collisions
label_mutated += label_hashed[:6]
return label_mutated
@classmethod
def _truncate_label(cls, label: str) -> str:
"""BigQuery requires column names start with either a letter or
underscore. To make sure this is always the case, an underscore is prefixed
to the md5 hash of the original label.
:param label: expected expression label
:return: truncated label
"""
return "_" + md5_sha_from_str(label)
@classmethod
def normalize_indexes(
cls, indexes: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
"""
Normalizes indexes for more consistency across db engines
:param indexes: Raw indexes as returned by SQLAlchemy
:return: cleaner, more aligned index definition
"""
normalized_idxs = []
# Fixing a bug/behavior observed in pybigquery==0.4.15 where
# the index's `column_names` == [None]
# Here we're returning only non-None indexes
for ix in indexes:
column_names = ix.get("column_names") or []
ix["column_names"] = [
col for col in column_names if col is not None
]
if ix["column_names"]:
normalized_idxs.append(ix)
return normalized_idxs
@classmethod
def extra_table_metadata(cls, database: "Database", table_name: str,
schema_name: Optional[str]) -> Dict[str, Any]:
indexes = database.get_indexes(table_name, schema_name)
if not indexes:
return {}
partitions_columns = [
index.get("column_names", []) for index in indexes
if index.get("name") == "partition"
]
cluster_columns = [
index.get("column_names", []) for index in indexes
if index.get("name") == "clustering"
]
return {
"partitions": {
"cols": partitions_columns
},
"clustering": {
"cols": cluster_columns
},
}
@classmethod
def epoch_to_dttm(cls) -> str:
return "TIMESTAMP_SECONDS({col})"
@classmethod
def epoch_ms_to_dttm(cls) -> str:
return "TIMESTAMP_MILLIS({col})"
@classmethod
def df_to_sql(
cls,
database: "Database",
table: Table,
df: pd.DataFrame,
to_sql_kwargs: Dict[str, Any],
) -> None:
"""
Upload data from a Pandas DataFrame to a database.
Calls `pandas_gbq.DataFrame.to_gbq` which requires `pandas_gbq` to be installed.
Note this method does not create metadata for the table.
:param database: The database to upload the data to
:param table: The table to upload the data to
:param df: The dataframe with data to be uploaded
:param to_sql_kwargs: The kwargs to be passed to pandas.DataFrame.to_sql` method
"""
try:
# pylint: disable=import-outside-toplevel
import pandas_gbq
from google.oauth2 import service_account
except ImportError as ex:
raise Exception(
"Could not import libraries `pandas_gbq` or `google.oauth2`, which are "
"required to be installed in your environment in order "
"to upload data to BigQuery") from ex
if not table.schema:
raise Exception("The table schema must be defined")
engine = cls.get_engine(database)
to_gbq_kwargs = {
"destination_table": str(table),
"project_id": engine.url.host
}
# Add credentials if they are set on the SQLAlchemy dialect.
creds = engine.dialect.credentials_info
if creds:
to_gbq_kwargs[
"credentials"] = service_account.Credentials.from_service_account_info(
creds)
# Only pass through supported kwargs.
supported_kwarg_keys = {"if_exists"}
for key in supported_kwarg_keys:
if key in to_sql_kwargs:
to_gbq_kwargs[key] = to_sql_kwargs[key]
pandas_gbq.to_gbq(df, **to_gbq_kwargs)
@classmethod
def build_sqlalchemy_uri(
cls,
parameters: BigQueryParametersType,
encrypted_extra: Optional[Dict[str, Any]] = None,
) -> str:
query = parameters.get("query", {})
query_params = urllib.parse.urlencode(query)
if encrypted_extra:
credentials_info = encrypted_extra.get("credentials_info")
if isinstance(credentials_info, str):
credentials_info = json.loads(credentials_info)
project_id = credentials_info.get("project_id")
if not encrypted_extra:
raise ValidationError("Missing service credentials")
if project_id:
return f"{cls.default_driver}://{project_id}/?{query_params}"
raise ValidationError("Invalid service credentials")
@classmethod
def get_parameters_from_uri(
cls,
uri: str,
encrypted_extra: Optional[Dict[str, str]] = None) -> Any:
value = make_url_safe(uri)
# Building parameters from encrypted_extra and uri
if encrypted_extra:
return {**encrypted_extra, "query": value.query}
raise ValidationError("Invalid service credentials")
@classmethod
def get_dbapi_exception_mapping(
cls) -> Dict[Type[Exception], Type[Exception]]:
# pylint: disable=import-outside-toplevel
from google.auth.exceptions import DefaultCredentialsError
return {DefaultCredentialsError: SupersetDBAPIDisconnectionError}
@classmethod
def validate_parameters(
cls,
parameters: BigQueryParametersType # pylint: disable=unused-argument
) -> List[SupersetError]:
return []
@classmethod
def parameters_json_schema(cls) -> Any:
"""
Return configuration parameters as OpenAPI.
"""
if not cls.parameters_schema:
return None
spec = APISpec(
title="Database Parameters",
version="1.0.0",
openapi_version="3.0.0",
plugins=[ma_plugin],
)
ma_plugin.init_spec(spec)
ma_plugin.converter.add_attribute_function(encrypted_field_properties)
spec.components.schema(cls.__name__, schema=cls.parameters_schema)
return spec.to_dict()["components"]["schemas"][cls.__name__]
@classmethod
def select_star( # pylint: disable=too-many-arguments
cls,
database: "Database",
table_name: str,
engine: Engine,
schema: Optional[str] = None,
limit: int = 100,
show_cols: bool = False,
indent: bool = True,
latest_partition: bool = True,
cols: Optional[List[Dict[str, Any]]] = None,
) -> str:
"""
Remove array structures from `SELECT *`.
BigQuery supports structures and arrays of structures, eg:
author STRUCT<name STRING, email STRING>
trailer ARRAY<STRUCT<key STRING, value STRING>>
When loading metadata for a table each key in the struct is displayed as a
separate pseudo-column, eg:
- author
- author.name
- author.email
- trailer
- trailer.key
- trailer.value
When generating the `SELECT *` statement we want to remove any keys from
structs inside an array, since selecting them results in an error. The correct
select statement should look like this:
SELECT
`author`,
`author`.`name`,
`author`.`email`,
`trailer`
FROM
table
Selecting `trailer.key` or `trailer.value` results in an error, as opposed to
selecting `author.name`, since they are keys in a structure inside an array.
This method removes any array pseudo-columns.
"""
if cols:
# For arrays of structs, remove the child columns, otherwise the query
# will fail.
array_prefixes = {
col["name"]
for col in cols if isinstance(col["type"], sqltypes.ARRAY)
}
cols = [
col for col in cols if "." not in col["name"]
or col["name"].split(".")[0] not in array_prefixes
]
return super().select_star(
database,
table_name,
engine,
schema,
limit,
show_cols,
indent,
latest_partition,
cols,
)
@classmethod
def _get_fields(cls, cols: List[Dict[str, Any]]) -> List[Any]:
"""
Label columns using their fully qualified name.
BigQuery supports columns of type `struct`, which are basically dictionaries.
When loading metadata for a table with struct columns, each key in the struct
is displayed as a separate pseudo-column, eg:
author STRUCT<name STRING, email STRING>
Will be shown as 3 columns:
- author
- author.name
- author.email
If we select those fields:
SELECT `author`, `author`.`name`, `author`.`email` FROM table
The resulting columns will be called "author", "name", and "email", This may
result in a clash with other columns. To prevent that, we explicitly label
the columns using their fully qualified name, so we end up with "author",
"author__name" and "author__email", respectively.
"""
return [
column(c["name"]).label(c["name"].replace(".", "__")) for c in cols
]
def custom_form_factory(form, field_types=None, field_slugs=None,
excluded_field_types=None,
registration_fields=False):
fields = (CustomField.query.filter_by(meeting_id=g.meeting.id)
.order_by(CustomField.sort))
if field_types:
fields = fields.filter(CustomField.field_type.in_(field_types))
if field_slugs:
fields = fields.filter(CustomField.slug.in_(field_slugs))
if excluded_field_types:
fields = fields.filter(
~CustomField.field_type.in_(excluded_field_types))
if registration_fields:
fields = fields.for_registration()
if getattr(form, 'CUSTOM_FIELDS_TYPE', None):
fields = fields.filter_by(custom_field_type=form.CUSTOM_FIELDS_TYPE)
form_attrs = {
'_custom_fields': OrderedMultiDict({c.slug: c for c in fields}),
}
for f in fields:
attrs = {'label': unicode(CustomFieldLabel(f.label)),
'validators': [],
'render_kw': {},
'description': f.hint}
data = _CUSTOM_FIELDS_MAP[f.field_type.code]
# overwrite data if _CUSTOM_FIELDS_MAP attribute is present on form
form_fields_map = getattr(form, '_CUSTOM_FIELDS_MAP', None)
if form_fields_map:
try:
data = form_fields_map[f.field_type.code]
except KeyError:
pass
if f.required:
attrs['validators'].append(DataRequired())
attrs['validators'].extend(data.get('validators', []))
if f.max_length:
attrs['validators'].append(Length(max=f.max_length))
if f.field_type.code == CustomField.SELECT:
query = CustomFieldChoice.query.filter_by(custom_field=f)
attrs['choices'] = [(unicode(c.value), __(c.value.english))
for c in query]
if not f.required:
attrs['choices'] = [('', '---')] + attrs['choices']
if f.slug == 'title':
attrs['choices'] = [choice for choice in attrs['choices']
if choice[0] in app.config['TITLE_CHOICES']]
attrs['coerce'] = unicode
if f.field_type.code == CustomField.LANGUAGE:
attrs['choices'] = [i for i in Participant.LANGUAGE_CHOICES
if i[0].lower() in app.config['TRANSLATIONS']]
if not f.required:
attrs['choices'] = [('', '---')] + attrs['choices']
attrs['coerce'] = unicode
if f.field_type.code == CustomField.CATEGORY:
query = Category.get_categories_for_meeting(
form.CUSTOM_FIELDS_TYPE)
if registration_fields:
query = query.filter_by(visible_on_registration_form=True)
attrs['choices'] = [(c.id, c) for c in query]
attrs['coerce'] = int
if f.field_type.code in (CustomField.MULTI_CHECKBOX, CustomField.RADIO):
query = CustomFieldChoice.query.filter_by(custom_field=f)
attrs['choices'] = [(unicode(c.value), c.value) for c in query]
attrs['coerce'] = unicode
if f.field_type.code == CustomField.IMAGE and f.photo_size and f.photo_size.code:
attrs['render_kw']["data-photoSize"] = f.photo_size.code
for coord in ("x1", "y1", "x2", "y2"):
form_attrs['%s_%s_' % (f.slug, coord)] = HiddenField(default=0)
# set field to form
# _set_rules_for_custom_fields(f, attrs)
field = data['field'](**attrs)
setattr(field, 'field_type', f.field_type.code)
form_attrs[f.slug] = field
form_attrs['rules'] = Rule.get_rules_for_fields(fields)
return type(form)(form.__name__, (form,), form_attrs)
elif obj.end_dttm < obj.start_dttm:
raise Exception('Annotation end time must be no earlier than start time.')
def pre_update(self, obj):
self.pre_add(obj)
class AnnotationLayerModelView(SupersetModelView, DeleteMixin):
datamodel = SQLAInterface(AnnotationLayer)
list_columns = ['id', 'name']
edit_columns = ['name', 'descr']
add_columns = edit_columns
appbuilder.add_view(
AnnotationLayerModelView,
'Annotation Layers',
label=__('Annotation Layers'),
icon='fa-comment',
category='Manage',
category_label=__('Manage'),
category_icon='')
appbuilder.add_view(
AnnotationModelView,
'Annotations',
label=__('Annotations'),
icon='fa-comments',
category='Manage',
category_label=__('Manage'),
category_icon='')
def init_views(self) -> None:
#
# We're doing local imports, as several of them import
# models which in turn try to import
# the global Flask app
#
# pylint: disable=import-outside-toplevel,too-many-locals,too-many-statements
from superset.annotation_layers.annotations.api import AnnotationRestApi
from superset.annotation_layers.api import AnnotationLayerRestApi
from superset.async_events.api import AsyncEventsRestApi
from superset.cachekeys.api import CacheRestApi
from superset.charts.api import ChartRestApi
from superset.charts.data.api import ChartDataRestApi
from superset.connectors.druid.views import (
Druid,
DruidClusterModelView,
DruidColumnInlineView,
DruidDatasourceModelView,
DruidMetricInlineView,
)
from superset.connectors.sqla.views import (
RowLevelSecurityFiltersModelView,
SqlMetricInlineView,
TableColumnInlineView,
TableModelView,
)
from superset.css_templates.api import CssTemplateRestApi
from superset.dashboards.api import DashboardRestApi
from superset.dashboards.filter_sets.api import FilterSetRestApi
from superset.dashboards.filter_state.api import DashboardFilterStateRestApi
from superset.databases.api import DatabaseRestApi
from superset.datasets.api import DatasetRestApi
from superset.datasets.columns.api import DatasetColumnsRestApi
from superset.datasets.metrics.api import DatasetMetricRestApi
from superset.explore.form_data.api import ExploreFormDataRestApi
from superset.queries.api import QueryRestApi
from superset.queries.saved_queries.api import SavedQueryRestApi
from superset.reports.api import ReportScheduleRestApi
from superset.reports.logs.api import ReportExecutionLogRestApi
from superset.security.api import SecurityRestApi
from superset.views.access_requests import AccessRequestsModelView
from superset.views.alerts import (
AlertLogModelView,
AlertModelView,
AlertObservationModelView,
AlertView,
ReportView,
)
from superset.views.annotations import (
AnnotationLayerModelView,
AnnotationModelView,
)
from superset.views.api import Api
from superset.views.chart.views import SliceAsync, SliceModelView
from superset.views.core import Superset
from superset.views.css_templates import (
CssTemplateAsyncModelView,
CssTemplateModelView,
)
from superset.views.dashboard.views import (
Dashboard,
DashboardModelView,
DashboardModelViewAsync,
)
from superset.views.database.views import (
ColumnarToDatabaseView,
CsvToDatabaseView,
DatabaseView,
ExcelToDatabaseView,
)
from superset.views.datasource.views import Datasource
from superset.views.dynamic_plugins import DynamicPluginsView
from superset.views.key_value import KV
from superset.views.log.api import LogRestApi
from superset.views.log.views import LogModelView
from superset.views.redirects import R
from superset.views.schedules import (
DashboardEmailScheduleView,
SliceEmailScheduleView,
)
from superset.views.sql_lab import (
SavedQueryView,
SavedQueryViewApi,
SqlLab,
TableSchemaView,
TabStateView,
)
from superset.views.tags import TagView
#
# Setup API views
#
appbuilder.add_api(AnnotationRestApi)
appbuilder.add_api(AnnotationLayerRestApi)
appbuilder.add_api(AsyncEventsRestApi)
appbuilder.add_api(CacheRestApi)
appbuilder.add_api(ChartRestApi)
appbuilder.add_api(ChartDataRestApi)
appbuilder.add_api(CssTemplateRestApi)
appbuilder.add_api(DashboardFilterStateRestApi)
appbuilder.add_api(DashboardRestApi)
appbuilder.add_api(DatabaseRestApi)
appbuilder.add_api(DatasetRestApi)
appbuilder.add_api(DatasetColumnsRestApi)
appbuilder.add_api(DatasetMetricRestApi)
appbuilder.add_api(ExploreFormDataRestApi)
appbuilder.add_api(FilterSetRestApi)
appbuilder.add_api(QueryRestApi)
appbuilder.add_api(ReportScheduleRestApi)
appbuilder.add_api(ReportExecutionLogRestApi)
appbuilder.add_api(SavedQueryRestApi)
#
# Setup regular views
#
appbuilder.add_link(
"Home",
label=__("Home"),
href="/superset/welcome/",
cond=lambda: bool(appbuilder.app.config["LOGO_TARGET_PATH"]),
)
appbuilder.add_view(
AnnotationLayerModelView,
"Annotation Layers",
label=__("Annotation Layers"),
icon="fa-comment",
category="Manage",
category_label=__("Manage"),
category_icon="",
)
appbuilder.add_view(
DashboardModelView,
"Dashboards",
label=__("Dashboards"),
icon="fa-dashboard",
category="",
category_icon="",
)
appbuilder.add_view(
SliceModelView,
"Charts",
label=__("Charts"),
icon="fa-bar-chart",
category="",
category_icon="",
)
appbuilder.add_view(
DynamicPluginsView,
"Plugins",
label=__("Plugins"),
category="Manage",
category_label=__("Manage"),
icon="fa-puzzle-piece",
menu_cond=lambda: feature_flag_manager.is_feature_enabled(
"DYNAMIC_PLUGINS"),
)
appbuilder.add_view(
CssTemplateModelView,
"CSS Templates",
label=__("CSS Templates"),
icon="fa-css3",
category="Manage",
category_label=__("Manage"),
category_icon="",
)
appbuilder.add_view(
RowLevelSecurityFiltersModelView,
"Row Level Security",
label=__("Row Level Security"),
category="Security",
category_label=__("Security"),
icon="fa-lock",
menu_cond=lambda: feature_flag_manager.is_feature_enabled(
"ROW_LEVEL_SECURITY"),
)
#
# Setup views with no menu
#
appbuilder.add_view_no_menu(Api)
appbuilder.add_view_no_menu(CssTemplateAsyncModelView)
appbuilder.add_view_no_menu(CsvToDatabaseView)
appbuilder.add_view_no_menu(ExcelToDatabaseView)
appbuilder.add_view_no_menu(ColumnarToDatabaseView)
appbuilder.add_view_no_menu(Dashboard)
appbuilder.add_view_no_menu(DashboardModelViewAsync)
appbuilder.add_view_no_menu(Datasource)
appbuilder.add_view_no_menu(KV)
appbuilder.add_view_no_menu(R)
appbuilder.add_view_no_menu(SavedQueryView)
appbuilder.add_view_no_menu(SavedQueryViewApi)
appbuilder.add_view_no_menu(SliceAsync)
appbuilder.add_view_no_menu(SqlLab)
appbuilder.add_view_no_menu(SqlMetricInlineView)
appbuilder.add_view_no_menu(AnnotationModelView)
appbuilder.add_view_no_menu(Superset)
appbuilder.add_view_no_menu(TableColumnInlineView)
appbuilder.add_view_no_menu(TableModelView)
appbuilder.add_view_no_menu(TableSchemaView)
appbuilder.add_view_no_menu(TabStateView)
appbuilder.add_view_no_menu(TagView)
#
# Add links
#
appbuilder.add_link(
"Import Dashboards",
label=__("Import Dashboards"),
href="/superset/import_dashboards/",
icon="fa-cloud-upload",
category="Manage",
category_label=__("Manage"),
category_icon="fa-wrench",
cond=lambda: not feature_flag_manager.is_feature_enabled(
"VERSIONED_EXPORT"),
)
appbuilder.add_link(
"SQL Editor",
label=_("SQL Editor"),
href="/superset/sqllab/",
category_icon="fa-flask",
icon="fa-flask",
category="SQL Lab",
category_label=__("SQL Lab"),
)
appbuilder.add_link(
__("Saved Queries"),
href="/savedqueryview/list/",
icon="fa-save",
category="SQL Lab",
)
appbuilder.add_link(
"Query Search",
label=_("Query History"),
href="/superset/sqllab/history/",
icon="fa-search",
category_icon="fa-flask",
category="SQL Lab",
category_label=__("SQL Lab"),
)
appbuilder.add_view(
DatabaseView,
"Databases",
label=__("Databases"),
icon="fa-database",
category="Data",
category_label=__("Data"),
category_icon="fa-database",
)
appbuilder.add_link(
"Datasets",
label=__("Datasets"),
href="/tablemodelview/list/",
icon="fa-table",
category="Data",
category_label=__("Data"),
category_icon="fa-table",
)
appbuilder.add_separator("Data")
appbuilder.add_api(LogRestApi)
appbuilder.add_view(
LogModelView,
"Action Log",
label=__("Action Log"),
category="Security",
category_label=__("Security"),
icon="fa-list-ol",
menu_cond=lambda: (self.config["FAB_ADD_SECURITY_VIEWS"] and self.
config["SUPERSET_LOG_VIEW"]),
)
appbuilder.add_api(SecurityRestApi)
#
# Conditionally setup email views
#
if self.config["ENABLE_SCHEDULED_EMAIL_REPORTS"]:
logging.warning(
"ENABLE_SCHEDULED_EMAIL_REPORTS "
"is deprecated and will be removed in version 2.0.0")
appbuilder.add_separator(
"Manage",
cond=lambda: self.config["ENABLE_SCHEDULED_EMAIL_REPORTS"])
appbuilder.add_view(
DashboardEmailScheduleView,
"Dashboard Email Schedules",
label=__("Dashboard Emails"),
category="Manage",
category_label=__("Manage"),
icon="fa-search",
menu_cond=lambda: self.config["ENABLE_SCHEDULED_EMAIL_REPORTS"],
)
appbuilder.add_view(
SliceEmailScheduleView,
"Chart Emails",
label=__("Chart Email Schedules"),
category="Manage",
category_label=__("Manage"),
icon="fa-search",
menu_cond=lambda: self.config["ENABLE_SCHEDULED_EMAIL_REPORTS"],
)
if self.config["ENABLE_ALERTS"]:
logging.warning(
"ENABLE_ALERTS is deprecated and will be removed in version 2.0.0"
)
appbuilder.add_view(
AlertModelView,
"Alerts",
label=__("Alerts"),
category="Manage",
category_label=__("Manage"),
icon="fa-exclamation-triangle",
menu_cond=lambda: bool(self.config["ENABLE_ALERTS"]),
)
appbuilder.add_view_no_menu(AlertLogModelView)
appbuilder.add_view_no_menu(AlertObservationModelView)
appbuilder.add_view(
AlertView,
"Alerts & Report",
label=__("Alerts & Reports"),
category="Manage",
category_label=__("Manage"),
icon="fa-exclamation-triangle",
menu_cond=lambda: feature_flag_manager.is_feature_enabled(
"ALERT_REPORTS"),
)
appbuilder.add_view_no_menu(ReportView)
appbuilder.add_view(
AccessRequestsModelView,
"Access requests",
label=__("Access requests"),
category="Security",
category_label=__("Security"),
icon="fa-table",
menu_cond=lambda: bool(self.config["ENABLE_ACCESS_REQUEST"]),
)
#
# Druid Views
#
appbuilder.add_separator(
"Data", cond=lambda: bool(self.config["DRUID_IS_ACTIVE"]))
appbuilder.add_view(
DruidDatasourceModelView,
"Druid Datasources",
label=__("Druid Datasources"),
category="Data",
category_label=__("Data"),
icon="fa-cube",
menu_cond=lambda: bool(self.config["DRUID_IS_ACTIVE"]),
)
appbuilder.add_view(
DruidClusterModelView,
name="Druid Clusters",
label=__("Druid Clusters"),
icon="fa-cubes",
category="Data",
category_label=__("Data"),
category_icon="fa-database",
menu_cond=lambda: bool(self.config["DRUID_IS_ACTIVE"]),
)
appbuilder.add_view_no_menu(DruidMetricInlineView)
appbuilder.add_view_no_menu(DruidColumnInlineView)
appbuilder.add_view_no_menu(Druid)
appbuilder.add_link(
"Scan New Datasources",
label=__("Scan New Datasources"),
href="/druid/scan_new_datasources/",
category="Data",
category_label=__("Data"),
category_icon="fa-database",
icon="fa-refresh",
cond=lambda: bool(self.config["DRUID_IS_ACTIVE"] and self.config[
"DRUID_METADATA_LINKS_ENABLED"]),
)
appbuilder.add_link(
"Refresh Druid Metadata",
label=__("Refresh Druid Metadata"),
href="/druid/refresh_datasources/",
category="Data",
category_label=__("Data"),
category_icon="fa-database",
icon="fa-cog",
cond=lambda: bool(self.config["DRUID_IS_ACTIVE"] and self.config[
"DRUID_METADATA_LINKS_ENABLED"]),
)
appbuilder.add_separator(
"Data", cond=lambda: bool(self.config["DRUID_IS_ACTIVE"]))
}
def pre_add(self, db):
conn = sqla.engine.url.make_url(db.sqlalchemy_uri)
db.password = conn.password
conn.password = "******" * 10 if conn.password else None
db.sqlalchemy_uri = str(conn) # hides the password
def pre_update(self, db):
self.pre_add(db)
appbuilder.add_view(
DatabaseView,
"Databases",
label=__("Databases"),
icon="fa-database",
category="Sources",
category_label=__("Sources"),
category_icon='fa-database',)
class TableModelView(CaravelModelView, DeleteMixin): # noqa
datamodel = SQLAInterface(models.SqlaTable)
list_columns = [
'table_link', 'database', 'sql_link', 'is_featured',
'changed_by_', 'changed_on_']
order_columns = [
'table_link', 'database', 'sql_link', 'is_featured', 'changed_on_']
add_columns = [
'table_name', 'database', 'schema',
YamlExportMixin): # pylint: disable=too-many-ancestors
datamodel = SQLAInterface(models.Database)
add_template = "superset/models/database/add.html"
edit_template = "superset/models/database/edit.html"
validators_columns = {"sqlalchemy_uri": [sqlalchemy_uri_form_validator]}
yaml_dict_key = "databases"
def _delete(self, pk):
DeleteMixin._delete(self, pk)
appbuilder.add_link(
"Import Dashboards",
label=__("Import Dashboards"),
href="/superset/import_dashboards",
icon="fa-cloud-upload",
category="Manage",
category_label=__("Manage"),
category_icon="fa-wrench",
)
appbuilder.add_view(
DatabaseView,
"Databases",
label=__("Databases"),
icon="fa-database",
category="Sources",
category_label=__("Sources"),
category_icon="fa-database",
}
def pre_add(self, cluster):
security_manager.merge_perm('database_access', cluster.perm)
def pre_update(self, cluster):
self.pre_add(cluster)
def _delete(self, pk):
DeleteMixin._delete(self, pk)
appbuilder.add_view(
DruidClusterModelView,
name='Druid Clusters',
label=__('Druid Clusters'),
icon='fa-cubes',
category='Sources',
category_label=__('Sources'),
category_icon='fa-database',
)
class DruidDatasourceModelView(DatasourceModelView, DeleteMixin, YamlExportMixin): # noqa
datamodel = SQLAInterface(models.DruidDatasource)
list_title = _('List Druid Datasource')
show_title = _('Show Druid Datasource')
add_title = _('Add Druid Datasource')
edit_title = _('Edit Druid Datasource')
def init_views(self) -> None:
#
# We're doing local imports, as several of them import
# models which in turn try to import
# the global Flask app
#
# pylint: disable=too-many-locals
# pylint: disable=too-many-statements
# pylint: disable=too-many-branches
from superset.annotation_layers.api import AnnotationLayerRestApi
from superset.annotation_layers.annotations.api import AnnotationRestApi
from superset.cachekeys.api import CacheRestApi
from superset.charts.api import ChartRestApi
from superset.connectors.druid.views import (
Druid,
DruidClusterModelView,
DruidColumnInlineView,
DruidDatasourceModelView,
DruidMetricInlineView,
)
from superset.connectors.sqla.views import (
RowLevelSecurityFiltersModelView,
SqlMetricInlineView,
TableColumnInlineView,
TableModelView,
)
from superset.css_templates.api import CssTemplateRestApi
from superset.dashboards.api import DashboardRestApi
from superset.databases.api import DatabaseRestApi
from superset.datasets.api import DatasetRestApi
from superset.queries.api import QueryRestApi
from superset.queries.saved_queries.api import SavedQueryRestApi
from superset.reports.api import ReportScheduleRestApi
from superset.reports.logs.api import ReportExecutionLogRestApi
from superset.views.access_requests import AccessRequestsModelView
from superset.views.alerts import (
AlertLogModelView,
AlertModelView,
AlertObservationModelView,
)
from superset.views.annotations import (
AnnotationLayerModelView,
AnnotationModelView,
)
from superset.views.api import Api
from superset.views.chart.views import SliceAsync, SliceModelView
from superset.views.core import Superset
from superset.views.css_templates import (
CssTemplateAsyncModelView,
CssTemplateModelView,
)
from superset.views.dashboard.views import (
Dashboard,
DashboardModelView,
DashboardModelViewAsync,
)
from superset.views.database.views import (
CsvToDatabaseView,
DatabaseView,
ExcelToDatabaseView,
)
from superset.views.datasource import Datasource
from superset.views.key_value import KV
from superset.views.log.api import LogRestApi
from superset.views.log.views import LogModelView
from superset.views.redirects import R
from superset.views.schedules import (
DashboardEmailScheduleView,
SliceEmailScheduleView,
)
from superset.views.sql_lab import (
SavedQueryView,
SavedQueryViewApi,
SqlLab,
TableSchemaView,
TabStateView,
)
from superset.views.tags import TagView
#
# Setup API views
#
appbuilder.add_api(AnnotationRestApi)
appbuilder.add_api(AnnotationLayerRestApi)
appbuilder.add_api(CacheRestApi)
appbuilder.add_api(ChartRestApi)
appbuilder.add_api(CssTemplateRestApi)
appbuilder.add_api(DashboardRestApi)
appbuilder.add_api(DatabaseRestApi)
appbuilder.add_api(DatasetRestApi)
appbuilder.add_api(QueryRestApi)
appbuilder.add_api(SavedQueryRestApi)
if feature_flag_manager.is_feature_enabled("ALERTS_REPORTS"):
appbuilder.add_api(ReportScheduleRestApi)
appbuilder.add_api(ReportExecutionLogRestApi)
#
# Setup regular views
#
appbuilder.add_view(
AnnotationLayerModelView,
"Annotation Layers",
label=__("Annotation Layers"),
icon="fa-comment",
category="Manage",
category_label=__("Manage"),
category_icon="",
)
appbuilder.add_view(
DatabaseView,
"Databases",
label=__("Databases"),
icon="fa-database",
category="Data",
category_label=__("Data"),
category_icon="fa-database",
)
appbuilder.add_link(
"Datasets",
label=__("Datasets"),
href="/tablemodelview/list/?_flt_1_is_sqllab_view=y",
icon="fa-table",
category="Data",
category_label=__("Data"),
category_icon="fa-table",
)
appbuilder.add_separator("Data")
appbuilder.add_view(
SliceModelView,
"Charts",
label=__("Charts"),
icon="fa-bar-chart",
category="",
category_icon="",
)
appbuilder.add_view(
DashboardModelView,
"Dashboards",
label=__("Dashboards"),
icon="fa-dashboard",
category="",
category_icon="",
)
appbuilder.add_view(
CssTemplateModelView,
"CSS Templates",
label=__("CSS Templates"),
icon="fa-css3",
category="Manage",
category_label=__("Manage"),
category_icon="",
)
if feature_flag_manager.is_feature_enabled("ROW_LEVEL_SECURITY"):
appbuilder.add_view(
RowLevelSecurityFiltersModelView,
"Row Level Security",
label=__("Row level security"),
category="Security",
category_label=__("Security"),
icon="fa-lock",
)
#
# Setup views with no menu
#
appbuilder.add_view_no_menu(Api)
appbuilder.add_view_no_menu(CssTemplateAsyncModelView)
appbuilder.add_view_no_menu(CsvToDatabaseView)
appbuilder.add_view_no_menu(ExcelToDatabaseView)
appbuilder.add_view_no_menu(Dashboard)
appbuilder.add_view_no_menu(DashboardModelViewAsync)
appbuilder.add_view_no_menu(Datasource)
if feature_flag_manager.is_feature_enabled("KV_STORE"):
appbuilder.add_view_no_menu(KV)
appbuilder.add_view_no_menu(R)
appbuilder.add_view_no_menu(SavedQueryView)
appbuilder.add_view_no_menu(SavedQueryViewApi)
appbuilder.add_view_no_menu(SliceAsync)
appbuilder.add_view_no_menu(SqlLab)
appbuilder.add_view_no_menu(SqlMetricInlineView)
appbuilder.add_view_no_menu(AnnotationModelView)
appbuilder.add_view_no_menu(Superset)
appbuilder.add_view_no_menu(TableColumnInlineView)
appbuilder.add_view_no_menu(TableModelView)
appbuilder.add_view_no_menu(TableSchemaView)
appbuilder.add_view_no_menu(TabStateView)
if feature_flag_manager.is_feature_enabled("TAGGING_SYSTEM"):
appbuilder.add_view_no_menu(TagView)
#
# Add links
#
appbuilder.add_link(
"Import Dashboards",
label=__("Import Dashboards"),
href="/superset/import_dashboards",
icon="fa-cloud-upload",
category="Manage",
category_label=__("Manage"),
category_icon="fa-wrench",
)
appbuilder.add_link(
"SQL Editor",
label=_("SQL Editor"),
href="/superset/sqllab",
category_icon="fa-flask",
icon="fa-flask",
category="SQL Lab",
category_label=__("SQL Lab"),
)
appbuilder.add_link(
__("Saved Queries"),
href="/sqllab/my_queries/",
icon="fa-save",
category="SQL Lab",
)
appbuilder.add_link(
"Query Search",
label=_("Query Search"),
href="/superset/sqllab#search",
icon="fa-search",
category_icon="fa-flask",
category="SQL Lab",
category_label=__("SQL Lab"),
)
if self.config["CSV_EXTENSIONS"].intersection(
self.config["ALLOWED_EXTENSIONS"]
):
appbuilder.add_link(
"Upload a CSV",
label=__("Upload a CSV"),
href="/csvtodatabaseview/form",
icon="fa-upload",
category="Data",
category_label=__("Data"),
category_icon="fa-wrench",
)
try:
import xlrd # pylint: disable=unused-import
if self.config["EXCEL_EXTENSIONS"].intersection(
self.config["ALLOWED_EXTENSIONS"]
):
appbuilder.add_link(
"Upload Excel",
label=__("Upload Excel"),
href="/exceltodatabaseview/form",
icon="fa-upload",
category="Data",
category_label=__("Data"),
category_icon="fa-wrench",
)
except ImportError:
pass
#
# Conditionally setup log views
#
if self.config["FAB_ADD_SECURITY_VIEWS"] and self.config["SUPERSET_LOG_VIEW"]:
appbuilder.add_api(LogRestApi)
appbuilder.add_view(
LogModelView,
"Action Log",
label=__("Action Log"),
category="Security",
category_label=__("Security"),
icon="fa-list-ol",
)
#
# Conditionally setup email views
#
if self.config["ENABLE_SCHEDULED_EMAIL_REPORTS"]:
appbuilder.add_separator("Manage")
appbuilder.add_view(
DashboardEmailScheduleView,
"Dashboard Email Schedules",
label=__("Dashboard Emails"),
category="Manage",
category_label=__("Manage"),
icon="fa-search",
)
appbuilder.add_view(
SliceEmailScheduleView,
"Chart Emails",
label=__("Chart Email Schedules"),
category="Manage",
category_label=__("Manage"),
icon="fa-search",
)
if self.config["ENABLE_ALERTS"]:
appbuilder.add_view(
AlertModelView,
"Alerts",
label=__("Alerts"),
category="Manage",
category_label=__("Manage"),
icon="fa-exclamation-triangle",
)
appbuilder.add_view_no_menu(AlertObservationModelView)
appbuilder.add_view_no_menu(AlertLogModelView)
#
# Conditionally add Access Request Model View
#
if self.config["ENABLE_ACCESS_REQUEST"]:
appbuilder.add_view(
AccessRequestsModelView,
"Access requests",
label=__("Access requests"),
category="Security",
category_label=__("Security"),
icon="fa-table",
)
#
# Conditionally setup Druid Views
#
if self.config["DRUID_IS_ACTIVE"]:
appbuilder.add_separator("Data")
appbuilder.add_view(
DruidDatasourceModelView,
"Druid Datasources",
label=__("Druid Datasources"),
category="Data",
category_label=__("Data"),
icon="fa-cube",
)
appbuilder.add_view(
DruidClusterModelView,
name="Druid Clusters",
label=__("Druid Clusters"),
icon="fa-cubes",
category="Data",
category_label=__("Data"),
category_icon="fa-database",
)
appbuilder.add_view_no_menu(DruidMetricInlineView)
appbuilder.add_view_no_menu(DruidColumnInlineView)
appbuilder.add_view_no_menu(Druid)
if self.config["DRUID_METADATA_LINKS_ENABLED"]:
appbuilder.add_link(
"Scan New Datasources",
label=__("Scan New Datasources"),
href="/druid/scan_new_datasources/",
category="Data",
category_label=__("Data"),
category_icon="fa-database",
icon="fa-refresh",
)
appbuilder.add_link(
"Refresh Druid Metadata",
label=__("Refresh Druid Metadata"),
href="/druid/refresh_datasources/",
category="Data",
category_label=__("Data"),
category_icon="fa-database",
icon="fa-cog",
)
appbuilder.add_separator("Data")
class QueryView(SupersetModelView):
datamodel = SQLAInterface(Query)
list_columns = ['user', 'database', 'status', 'start_time', 'end_time']
label_columns = {
'user': _('User'),
'database': _('Database'),
'status': _('Status'),
'start_time': _('Start Time'),
'end_time': _('End Time'),
}
appbuilder.add_view(
QueryView,
'Queries',
label=__('Queries'),
category='Manage',
category_label=__('Manage'),
icon='fa-search')
class SavedQueryView(SupersetModelView, DeleteMixin):
datamodel = SQLAInterface(SavedQuery)
list_title = _('List Saved Query')
show_title = _('Show Saved Query')
add_title = _('Add Saved Query')
edit_title = _('Edit Saved Query')
list_columns = [
'label', 'user', 'database', 'schema', 'description',
t.fetch_metadata()
successes.append(t)
except Exception:
failures.append(t)
if len(successes) > 0:
success_msg = _(
'Metadata refreshed for the following table(s): %(tables)s',
tables=', '.join([t.table_name for t in successes]))
flash(success_msg, 'info')
if len(failures) > 0:
failure_msg = _(
'Unable to retrieve metadata for the following table(s): %(tables)s',
tables=', '.join([t.table_name for t in failures]))
flash(failure_msg, 'danger')
return redirect('/tablemodelview/list/')
appbuilder.add_view_no_menu(TableModelView)
appbuilder.add_link(
'Tables',
label=__('Tables'),
href='/tablemodelview/list/?_flt_1_is_sqllab_view=y',
icon='fa-table',
category='Sources',
category_label=__('Sources'),
category_icon='fa-table')
appbuilder.add_separator('Sources')
class NNI_ModelView_Base():
datamodel = SQLAInterface(NNI)
conv = GeneralModelConverter(datamodel)
label_title = 'nni超参搜索'
check_redirect_list_url = '/nni_modelview/list/'
help_url = conf.get('HELP_URL', {}).get(datamodel.obj.__tablename__,
'') if datamodel else ''
base_permissions = [
'can_add', 'can_edit', 'can_delete', 'can_list', 'can_show'
] # 默认为这些
base_order = ('id', 'desc')
base_filters = [["id", NNI_Filter, lambda: []]] # 设置权限过滤器
order_columns = ['id']
list_columns = [
'project', 'describe_url', 'job_type', 'creator', 'modified', 'run',
'log'
]
show_columns = [
'created_by', 'changed_by', 'created_on', 'changed_on', 'job_type',
'name', 'namespace', 'describe', 'parallel_trial_count',
'max_trial_count', 'objective_type', 'objective_goal',
'objective_metric_name', 'objective_additional_metric_names',
'algorithm_name', 'algorithm_setting', 'parameters_html',
'trial_spec_html', 'working_dir', 'volume_mount', 'node_selector',
'image_pull_policy', 'resource_memory', 'resource_cpu', 'resource_gpu',
'experiment_html', 'alert_status'
]
add_form_query_rel_fields = {
"project": [["name", Project_Join_Filter, 'org']]
}
edit_form_query_rel_fields = add_form_query_rel_fields
edit_form_extra_fields = {}
edit_form_extra_fields["alert_status"] = MySelectMultipleField(
label=_(datamodel.obj.lab('alert_status')),
widget=Select2ManyWidget(),
# default=datamodel.obj.alert_status.default.arg,
choices=[[x, x] for x in [
'Pending', 'Running', 'Succeeded', 'Failed', 'Unknown', 'Waiting',
'Terminated'
]],
description="选择通知状态",
)
edit_form_extra_fields['name'] = StringField(
_(datamodel.obj.lab('name')),
description='英文名(字母、数字、- 组成)，最长50个字符',
widget=BS3TextFieldWidget(),
validators=[
DataRequired(),
Regexp("^[a-z][a-z0-9\-]*[a-z0-9]$"),
Length(1, 54)
])
edit_form_extra_fields['describe'] = StringField(
_(datamodel.obj.lab('describe')),
description='中文描述',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
edit_form_extra_fields['namespace'] = StringField(
_(datamodel.obj.lab('namespace')),
description='运行命名空间',
widget=BS3TextFieldWidget(),
default=datamodel.obj.namespace.default.arg,
validators=[DataRequired()])
edit_form_extra_fields['parallel_trial_count'] = IntegerField(
_(datamodel.obj.lab('parallel_trial_count')),
default=datamodel.obj.parallel_trial_count.default.arg,
description='可并行的计算实例数目',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
edit_form_extra_fields['max_trial_count'] = IntegerField(
_(datamodel.obj.lab('max_trial_count')),
default=datamodel.obj.max_trial_count.default.arg,
description='最大并行的计算实例数目',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
edit_form_extra_fields['max_failed_trial_count'] = IntegerField(
_(datamodel.obj.lab('max_failed_trial_count')),
default=datamodel.obj.max_failed_trial_count.default.arg,
description='最大失败的计算实例数目',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
edit_form_extra_fields['objective_type'] = SelectField(
_(datamodel.obj.lab('objective_type')),
default=datamodel.obj.objective_type.default.arg,
description='目标函数类型（和自己代码中对应）',
widget=Select2Widget(),
choices=[['maximize', 'maximize'], ['minimize', 'minimize']],
validators=[DataRequired()])
edit_form_extra_fields['objective_goal'] = FloatField(
_(datamodel.obj.lab('objective_goal')),
default=datamodel.obj.objective_goal.default.arg,
description='目标门限',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
edit_form_extra_fields['objective_metric_name'] = StringField(
_(datamodel.obj.lab('objective_metric_name')),
default=NNI.objective_metric_name.default.arg,
description='目标函数（和自己代码中对应）',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
edit_form_extra_fields['objective_additional_metric_names'] = StringField(
_(datamodel.obj.lab('objective_additional_metric_names')),
default=datamodel.obj.objective_additional_metric_names.default.arg,
description='其他目标函数（和自己代码中对应）',
widget=BS3TextFieldWidget())
algorithm_name_choices = {
'TPE': "TPE",
'Random': "随机搜索",
"Anneal": "退火算法",
"Evolution": "进化算法",
"SMAC": "SMAC",
"BatchTuner": "批量调参器",
"GridSearch": "网格搜索",
"Hyperband": "Hyperband",
"NetworkMorphism": "Network Morphism",
"MetisTuner": "Metis Tuner",
"BOHB": "BOHB Advisor",
"GPTuner": "GP Tuner",
"PPOTuner": "PPO Tuner",
"PBTTuner": "PBT Tuner"
}
algorithm_name_choices = list(algorithm_name_choices.items())
edit_form_extra_fields['algorithm_name'] = SelectField(
_(datamodel.obj.lab('algorithm_name')),
default=datamodel.obj.algorithm_name.default.arg,
description='搜索算法',
widget=Select2Widget(),
choices=algorithm_name_choices,
validators=[DataRequired()])
edit_form_extra_fields['algorithm_setting'] = StringField(
_(datamodel.obj.lab('algorithm_setting')),
default=datamodel.obj.algorithm_setting.default.arg,
widget=BS3TextFieldWidget(),
description='搜索算法配置')
edit_form_extra_fields['parameters_demo'] = StringField(
_(datamodel.obj.lab('parameters_demo')),
description='搜索参数示例，标准json格式，注意：所有整型、浮点型都写成字符串型',
widget=MyCodeArea(code=core.nni_parameters_demo()),
)
edit_form_extra_fields['parameters'] = StringField(
_(datamodel.obj.lab('parameters')),
default=datamodel.obj.parameters.default.arg,
description='搜索参数，注意：所有整型、浮点型都写成字符串型',
widget=MyBS3TextAreaFieldWidget(rows=10),
validators=[DataRequired()])
edit_form_extra_fields['node_selector'] = StringField(
_(datamodel.obj.lab('node_selector')),
description="部署task所在的机器(目前无需填写)",
default=datamodel.obj.node_selector.default.arg,
widget=BS3TextFieldWidget())
edit_form_extra_fields['working_dir'] = StringField(
_(datamodel.obj.lab('working_dir')),
description="代码所在目录，nni代码、配置和log都将在/mnt/${your_name}/nni/目录下进行",
default=datamodel.obj.working_dir.default.arg,
widget=BS3TextFieldWidget())
edit_form_extra_fields['image_pull_policy'] = SelectField(
_(datamodel.obj.lab('image_pull_policy')),
description="镜像拉取策略(always为总是拉取远程镜像，IfNotPresent为若本地存在则使用本地镜像)",
widget=Select2Widget(),
choices=[['Always', 'Always'], ['IfNotPresent', 'IfNotPresent']])
edit_form_extra_fields['volume_mount'] = StringField(
_(datamodel.obj.lab('volume_mount')),
description=
'外部挂载，格式:$pvc_name1(pvc):/$container_path1,$pvc_name2(pvc):/$container_path2',
default=datamodel.obj.volume_mount.default.arg,
widget=BS3TextFieldWidget())
edit_form_extra_fields['resource_memory'] = StringField(
_(datamodel.obj.lab('resource_memory')),
default=datamodel.obj.resource_memory.default.arg,
description='内存的资源使用限制(每个测试实例)，示例：1G，20G',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
edit_form_extra_fields['resource_cpu'] = StringField(
_(datamodel.obj.lab('resource_cpu')),
default=datamodel.obj.resource_cpu.default.arg,
description='cpu的资源使用限制(每个测试实例)(单位：核)，示例：2',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
@pysnooper.snoop()
def set_column(self, nni=None):
# 对编辑进行处理
request_data = request.args.to_dict()
job_type = request_data.get('job_type', '')
if nni:
job_type = nni.job_type
job_type_choices = ['', 'Job']
job_type_choices = [[job_type_choice, job_type_choice]
for job_type_choice in job_type_choices]
if nni:
self.edit_form_extra_fields['job_type'] = SelectField(
_(self.datamodel.obj.lab('job_type')),
description="超参搜索的任务类型",
choices=job_type_choices,
widget=MySelect2Widget(extra_classes="readonly",
value=job_type),
validators=[DataRequired()])
else:
self.edit_form_extra_fields['job_type'] = SelectField(
_(self.datamodel.obj.lab('job_type')),
description="超参搜索的任务类型",
widget=MySelect2Widget(new_web=True, value=job_type),
choices=job_type_choices,
validators=[DataRequired()])
self.edit_form_extra_fields['tf_worker_num'] = IntegerField(
_(self.datamodel.obj.lab('tf_worker_num')),
default=json.loads(nni.job_json).get('tf_worker_num', 3)
if nni and nni.job_json else 3,
description='工作节点数目',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
self.edit_form_extra_fields['tf_worker_image'] = StringField(
_(self.datamodel.obj.lab('tf_worker_image')),
default=json.loads(nni.job_json).get(
'tf_worker_image', conf.get('NNI_TFJOB_DEFAULT_IMAGE', '')) if
nni and nni.job_json else conf.get('NNI_TFJOB_DEFAULT_IMAGE', ''),
description='工作节点镜像',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
self.edit_form_extra_fields['tf_worker_command'] = StringField(
_(self.datamodel.obj.lab('tf_worker_command')),
default=json.loads(nni.job_json).get('tf_worker_command',
'python xx.py')
if nni and nni.job_json else 'python xx.py',
description='工作节点启动命令',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
self.edit_form_extra_fields['job_worker_image'] = StringField(
_(self.datamodel.obj.lab('job_worker_image')),
default=json.loads(nni.job_json).get(
'job_worker_image', conf.get('NNI_JOB_DEFAULT_IMAGE', ''))
if nni and nni.job_json else conf.get('NNI_JOB_DEFAULT_IMAGE', ''),
description='工作节点镜像',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
self.edit_form_extra_fields['job_worker_command'] = StringField(
_(self.datamodel.obj.lab('job_worker_command')),
default=json.loads(nni.job_json).get('job_worker_command',
'python xx.py')
if nni and nni.job_json else 'python xx.py',
description='工作节点启动命令',
widget=MyBS3TextAreaFieldWidget(),
validators=[DataRequired()])
self.edit_form_extra_fields['pytorch_worker_num'] = IntegerField(
_(self.datamodel.obj.lab('pytorch_worker_num')),
default=json.loads(nni.job_json).get('pytorch_worker_num', 3)
if nni and nni.job_json else 3,
description='工作节点数目',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
self.edit_form_extra_fields['pytorch_worker_image'] = StringField(
_(self.datamodel.obj.lab('pytorch_worker_image')),
default=json.loads(nni.job_json).get(
'pytorch_worker_image',
conf.get('NNI_PYTORCHJOB_DEFAULT_IMAGE', '')) if nni
and nni.job_json else conf.get('NNI_PYTORCHJOB_DEFAULT_IMAGE', ''),
description='工作节点镜像',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
self.edit_form_extra_fields['pytorch_master_command'] = StringField(
_(self.datamodel.obj.lab('pytorch_master_command')),
default=json.loads(nni.job_json).get('pytorch_master_command',
'python xx.py')
if nni and nni.job_json else 'python xx.py',
description='master节点启动命令',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
self.edit_form_extra_fields['pytorch_worker_command'] = StringField(
_(self.datamodel.obj.lab('pytorch_worker_command')),
default=json.loads(nni.job_json).get('pytorch_worker_command',
'python xx.py')
if nni and nni.job_json else 'python xx.py',
description='工作节点启动命令',
widget=BS3TextFieldWidget(),
validators=[DataRequired()])
self.edit_columns = [
'job_type', 'project', 'name', 'namespace', 'describe',
'parallel_trial_count', 'max_trial_count', 'objective_type',
'objective_goal', 'objective_metric_name',
'objective_additional_metric_names', 'algorithm_name',
'algorithm_setting', 'parameters_demo', 'parameters'
]
self.edit_fieldsets = [(
lazy_gettext('common'),
{
"fields": copy.deepcopy(self.edit_columns),
"expanded": True
},
)]
if job_type == 'TFJob':
group_columns = [
'tf_worker_num', 'tf_worker_image', 'tf_worker_command'
]
self.edit_fieldsets.append((
lazy_gettext(job_type),
{
"fields": group_columns,
"expanded": True
},
))
for column in group_columns:
self.edit_columns.append(column)
if job_type == 'Job':
group_columns = ['job_worker_image', 'job_worker_command']
self.edit_fieldsets.append((
lazy_gettext(job_type),
{
"fields": group_columns,
"expanded": True
},
))
for column in group_columns:
self.edit_columns.append(column)
if job_type == 'PyTorchJob':
group_columns = [
'pytorch_worker_num', 'pytorch_worker_image',
'pytorch_master_command', 'pytorch_worker_command'
]
self.edit_fieldsets.append((
lazy_gettext(job_type),
{
"fields": group_columns,
"expanded": True
},
))
for column in group_columns:
self.edit_columns.append(column)
if job_type == 'XGBoostJob':
group_columns = [
'pytorchjob_worker_image', 'pytorchjob_worker_command'
]
self.edit_fieldsets.append((
lazy_gettext(job_type),
{
"fields": group_columns,
"expanded": True
},
))
for column in group_columns:
self.edit_columns.append(column)
task_column = ['working_dir', 'resource_memory', 'resource_cpu']
self.edit_fieldsets.append((
lazy_gettext('task args'),
{
"fields": task_column,
"expanded": True
},
))
for column in task_column:
self.edit_columns.append(column)
self.edit_fieldsets.append((
lazy_gettext('run experiment'),
{
"fields": ['alert_status'],
"expanded": True
},
))
self.edit_columns.append('alert_status')
self.add_form_extra_fields = self.edit_form_extra_fields
self.add_fieldsets = self.edit_fieldsets
self.add_columns = self.edit_columns
pre_add_get = set_column
pre_update_get = set_column
# 处理form请求
def process_form(self, form, is_created):
# from flask_appbuilder.forms import DynamicForm
if 'parameters_demo' in form._fields:
del form._fields['parameters_demo'] # 不处理这个字段
# @pysnooper.snoop()
def deploy_nni_service(self, nni, command):
image_secrets = conf.get('HUBSECRET', [])
user_hubsecrets = db.session.query(Repository.hubsecret).filter(
Repository.created_by_fk == g.user.id).all()
if user_hubsecrets:
for hubsecret in user_hubsecrets:
if hubsecret[0] not in image_secrets:
image_secrets.append(hubsecret[0])
from myapp.utils.py.py_k8s import K8s
k8s_client = K8s(nni.project.cluster.get('KUBECONFIG', ''))
namespace = conf.get('KATIB_NAMESPACE')
run_id = 'nni-' + nni.name
try:
nni_deploy = k8s_client.AppsV1Api.read_namespaced_deployment(
name=nni.name, namespace=namespace)
if nni_deploy:
print('exist nni deploy')
k8s_client.AppsV1Api.delete_namespaced_deployment(
name=nni.name, namespace=namespace)
# return
except Exception as e:
print(e)
volume_mount = nni.volume_mount + ",/usr/share/zoneinfo/Asia/Shanghai(hostpath):/etc/localtime"
labels = {
"nni": nni.name,
"username": nni.created_by.username,
'run-id': run_id
}
k8s_client.create_debug_pod(
namespace=namespace,
name=nni.name,
labels=labels,
command=command,
args=None,
volume_mount=volume_mount,
working_dir='/mnt/%s' % nni.created_by.username,
node_selector=nni.get_node_selector(),
resource_memory='2G',
resource_cpu='2',
resource_gpu='0',
image_pull_policy=conf.get('IMAGE_PULL_POLICY', 'Always'),
image_pull_secrets=image_secrets,
image=conf.get('NNI_IMAGES',
json.loads(nni.job_json).get('job_worker_image')),
hostAliases=conf.get('HOSTALIASES', ''),
env=None,
privileged=False,
accounts='nni',
username=nni.created_by.username)
k8s_client.create_service(namespace=namespace,
name=nni.name,
username=nni.created_by.username,
ports=[8888],
selector=labels)
host = nni.project.cluster.get('NNI_DOMAIN', request.host)
if not host:
host = request.host
if ':' in host:
host = host[:host.rindex(':')] # 如果捕获到端口号，要去掉
vs_json = {
"apiVersion": "networking.istio.io/v1alpha3",
"kind": "VirtualService",
"metadata": {
"name": nni.name,
"namespace": namespace
},
"spec": {
"gateways": ["kubeflow/kubeflow-gateway"],
"hosts": ["*" if core.checkip(host) else host],
"http": [{
"match": [{
"uri": {
"prefix": "/nni/%s//" % nni.name
}
}, {
"uri": {
"prefix": "/nni/%s/" % nni.name
}
}],
"rewrite": {
"uri": "/nni/%s/" % nni.name
},
"route": [{
"destination": {
"host":
"%s.%s.svc.cluster.local" % (nni.name, namespace),
"port": {
"number": 8888
}
}
}],
"timeout":
"300s"
}]
}
}
crd_info = conf.get('CRD_INFO')['virtualservice']
k8s_client.delete_istio_ingress(namespace=namespace, name=nni.name)
k8s_client.create_crd(group=crd_info['group'],
version=crd_info['version'],
plural=crd_info['plural'],
namespace=namespace,
body=vs_json)
# 生成实验
# @pysnooper.snoop()
@expose('/run/<nni_id>', methods=['GET', 'POST'])
# @pysnooper.snoop()
def run(self, nni_id):
nni = db.session.query(NNI).filter(NNI.id == nni_id).first()
image_secrets = conf.get('HUBSECRET', [])
user_hubsecrets = db.session.query(Repository.hubsecret).filter(
Repository.created_by_fk == g.user.id).all()
if user_hubsecrets:
for hubsecret in user_hubsecrets:
if hubsecret[0] not in image_secrets:
image_secrets.append(hubsecret[0])
image_secrets = str(image_secrets)
trial_template = f'''
apiVersion: frameworkcontroller.microsoft.com/v1
kind: Framework
metadata:
name: {nni.name}
namespace: {nni.namespace}
spec:
executionType: Start
retryPolicy:
fancyRetryPolicy: true
maxRetryCount: 2
taskRoles:
- name: worker
taskNumber: 1
frameworkAttemptCompletionPolicy:
minFailedTaskCount: 1
minSucceededTaskCount: 3
task:
retryPolicy:
fancyRetryPolicy: false
maxRetryCount: 0
podGracefulDeletionTimeoutSec: 1800
pod:
spec:
restartPolicy: Never
hostNetwork: false
imagePullSecrets: {image_secrets}
containers:
- name: {nni.name}
image: {json.loads(nni.job_json).get("job_worker_image",'')}
command: {json.loads(nni.job_json).get("job_worker_command",'').split(' ')}
ports:
- containerPort: 5001
volumeMounts:
- name: frameworkbarrier-volume
mountPath: /mnt/frameworkbarrier
- name: data-volume
mountPath: /tmp/mount
serviceAccountName: frameworkbarrier
initContainers:
- name: frameworkbarrier
image: frameworkcontroller/frameworkbarrier
imagePullPolicy: IfNotPresent
volumeMounts:
- name: frameworkbarrier-volume
mountPath: /mnt/frameworkbarrier
volumes:
- name: frameworkbarrier-volume
emptyDir: {{}}
- name: data-volume
hostPath:
path: {conf.get('WORKSPACE_HOST_PATH','')}/{nni.created_by.username}/nni/{nni.name}
'''
controll_yaml = f'''
authorName: default
experimentName: {nni.name}
trialConcurrency: {nni.parallel_trial_count}
maxExecDuration: {nni.maxExecDuration}s
maxTrialNum: {nni.max_trial_count}
logLevel: info
logCollection: none
#choice: local, remote, pai, kubeflow
trainingServicePlatform: frameworkcontroller
searchSpacePath: /mnt/{nni.created_by.username}/nni/{nni.name}/search_space.json
#choice: true, false
useAnnotation: false
tuner:
#choice: TPE, Random, Anneal, Evolution, BatchTuner, MetisTuner, GPTuner
builtinTunerName: {nni.algorithm_name}
trial:
codeDir: {nni.working_dir}
frameworkcontrollerConfig:
namespace: {conf.get('KATIB_NAMESPACE','katib')}
storage: pvc
configPath: /mnt/{nni.created_by.username}/nni/{nni.name}/trial_template.yaml
pvc:
path: "/mnt/{nni.created_by.username}/nni/{nni.name}/"
'''
code_dir = "%s/%s/nni/%s" % (conf.get(
'WORKSPACE_HOST_PATH', ''), nni.created_by.username, nni.name)
if not os.path.exists(code_dir):
os.makedirs(code_dir)
trial_template_path = os.path.join(code_dir, 'trial_template.yaml')
file = open(trial_template_path, mode='w')
file.write(trial_template)
file.close()
controll_template_path = os.path.join(code_dir,
'controll_template.yaml')
file = open(controll_template_path, mode='w')
file.write(controll_yaml)
file.close()
searchSpacePath = os.path.join(code_dir, 'search_space.json')
file = open(searchSpacePath, mode='w')
file.write(nni.parameters)
file.close()
flash('nni服务部署完成', category='success')
# 执行启动命令
# command = ['bash','-c','mkdir -p /nni/nni_node/static/nni/%s && cp -r /nni/nni_node/static/* /nni/nni_node/static/nni/%s/ ; nnictl create --config /mnt/%s/nni/%s/controll_template.yaml -p 8888 --foreground --url_prefix nni/%s'%(nni.name,nni.name,nni.created_by.username,nni.name,nni.name)]
# command = ['bash', '-c','nnictl create --config /mnt/%s/nni/%s/controll_template.yaml -p 8888 --foreground' % (nni.created_by.username, nni.name)]
command = [
'bash', '-c',
'nnictl create --config /mnt/%s/nni/%s/controll_template.yaml -p 8888 --foreground --url_prefix nni/%s'
% (nni.created_by.username, nni.name, nni.name)
]
print(command)
self.deploy_nni_service(nni, command)
return redirect('/nni_modelview/list/')
# @pysnooper.snoop(watch_explode=())
def merge_trial_spec(self, item):
image_secrets = conf.get('HUBSECRET', [])
user_hubsecrets = db.session.query(Repository.hubsecret).filter(
Repository.created_by_fk == g.user.id).all()
if user_hubsecrets:
for hubsecret in user_hubsecrets:
if hubsecret[0] not in image_secrets:
image_secrets.append(hubsecret[0])
image_secrets = [{"name": hubsecret} for hubsecret in image_secrets]
item.job_json = {}
if item.job_type == 'TFJob':
item.trial_spec = core.merge_tfjob_experiment_template(
worker_num=item.tf_worker_num,
node_selector=item.get_node_selector(),
volume_mount=item.volume_mount,
image=item.tf_worker_image,
image_secrets=image_secrets,
hostAliases=conf.get('HOSTALIASES', ''),
workingDir=item.working_dir,
image_pull_policy=conf.get('IMAGE_PULL_POLICY', 'Always'),
resource_memory=item.resource_memory,
resource_cpu=item.resource_cpu,
command=item.tf_worker_command)
item.job_json = {
"tf_worker_num": item.tf_worker_num,
"tf_worker_image": item.tf_worker_image,
"tf_worker_command": item.tf_worker_command,
}
if item.job_type == 'Job':
item.trial_spec = core.merge_job_experiment_template(
node_selector=item.get_node_selector(),
volume_mount=item.volume_mount,
image=item.job_worker_image,
image_secrets=image_secrets,
hostAliases=conf.get('HOSTALIASES', ''),
workingDir=item.working_dir,
image_pull_policy=conf.get('IMAGE_PULL_POLICY', 'Always'),
resource_memory=item.resource_memory,
resource_cpu=item.resource_cpu,
command=item.job_worker_command)
item.job_json = {
"job_worker_image": item.job_worker_image,
"job_worker_command": item.job_worker_command,
}
if item.job_type == 'PyTorchJob':
item.trial_spec = core.merge_pytorchjob_experiment_template(
worker_num=item.pytorch_worker_num,
node_selector=item.get_node_selector(),
volume_mount=item.volume_mount,
image=item.pytorch_worker_image,
image_secrets=image_secrets,
hostAliases=conf.get('HOSTALIASES', ''),
workingDir=item.working_dir,
image_pull_policy=conf.get('IMAGE_PULL_POLICY', 'Always'),
resource_memory=item.resource_memory,
resource_cpu=item.resource_cpu,
master_command=item.pytorch_master_command,
worker_command=item.pytorch_worker_command)
item.job_json = {
"pytorch_worker_num": item.pytorch_worker_num,
"pytorch_worker_image": item.pytorch_worker_image,
"pytorch_master_command": item.pytorch_master_command,
"pytorch_worker_command": item.pytorch_worker_command,
}
item.job_json = json.dumps(item.job_json, indent=4, ensure_ascii=False)
# 检验参数是否有效
# @pysnooper.snoop()
def validate_parameters(self, parameters, algorithm):
return parameters
@expose("/log/<nni_id>", methods=["GET", "POST"])
def log_task(self, nni_id):
nni = db.session.query(NNI).filter_by(id=nni_id).first()
from myapp.utils.py.py_k8s import K8s
k8s = K8s(nni.project.cluster.get('KUBECONFIG', ''))
namespace = conf.get('KATIB_NAMESPACE')
pod = k8s.get_pods(namespace=namespace, pod_name=nni.name)
if pod:
pod = pod[0]
return redirect("/myapp/web/log/%s/%s/%s" %
(nni.project.cluster['NAME'], namespace, nni.name))
flash("未检测到当前搜索正在运行的容器", category='success')
return redirect('/nni_modelview/list/')
# @pysnooper.snoop()
def pre_add(self, item):
if item.job_type is None:
raise MyappException("Job type is mandatory")
if not item.volume_mount:
item.volume_mount = item.project.volume_mount
core.validate_json(item.parameters)
item.parameters = self.validate_parameters(item.parameters,
item.algorithm_name)
item.resource_memory = core.check_resource_memory(
item.resource_memory,
self.src_item_json.get('resource_memory', None)
if self.src_item_json else None)
item.resource_cpu = core.check_resource_cpu(
item.resource_cpu,
self.src_item_json.get('resource_cpu', None)
if self.src_item_json else None)
self.merge_trial_spec(item)
# self.make_experiment(item)
def pre_update(self, item):
self.pre_add(item)
@action("copy",
__("Copy NNI Experiment"),
confirmation=__('Copy NNI Experiment'),
icon="fa-copy",
multiple=True,
single=False)
def copy(self, nnis):
if not isinstance(nnis, list):
nnis = [nnis]
for nni in nnis:
new_nni = nni.clone()
new_nni.name = new_nni.name + "-copy"
new_nni.describe = new_nni.describe + "-copy"
new_nni.created_on = datetime.datetime.now()
new_nni.changed_on = datetime.datetime.now()
db.session.add(new_nni)
db.session.commit()
return redirect(request.referrer)
class AthenaEngineSpec(BaseEngineSpec):
engine = "awsathena"
engine_name = "Amazon Athena"
allows_escaped_colons = False
_time_grain_expressions = {
None:
"{col}",
"PT1S":
"date_trunc('second', CAST({col} AS TIMESTAMP))",
"PT1M":
"date_trunc('minute', CAST({col} AS TIMESTAMP))",
"PT1H":
"date_trunc('hour', CAST({col} AS TIMESTAMP))",
"P1D":
"date_trunc('day', CAST({col} AS TIMESTAMP))",
"P1W":
"date_trunc('week', CAST({col} AS TIMESTAMP))",
"P1M":
"date_trunc('month', CAST({col} AS TIMESTAMP))",
"P3M":
"date_trunc('quarter', CAST({col} AS TIMESTAMP))",
"P1Y":
"date_trunc('year', CAST({col} AS TIMESTAMP))",
"P1W/1970-01-03T00:00:00Z":
"date_add('day', 5, date_trunc('week', \
date_add('day', 1, CAST({col} AS TIMESTAMP))))",
"1969-12-28T00:00:00Z/P1W":
"date_add('day', -1, date_trunc('week', \
date_add('day', 1, CAST({col} AS TIMESTAMP))))",
}
custom_errors: Dict[Pattern[str], Tuple[str, SupersetErrorType, Dict[
str, Any]]] = {
SYNTAX_ERROR_REGEX: (
__("Please check your query for syntax errors at or "
'near "%(syntax_error)s". Then, try running your query again.'
),
SupersetErrorType.SYNTAX_ERROR,
{},
),
}
@classmethod
def convert_dttm(
cls,
target_type: str,
dttm: datetime,
db_extra: Optional[Dict[str, Any]] = None) -> Optional[str]:
tt = target_type.upper()
if tt == utils.TemporalType.DATE:
return f"from_iso8601_date('{dttm.date().isoformat()}')"
if tt == utils.TemporalType.TIMESTAMP:
datetime_formatted = dttm.isoformat(timespec="microseconds")
return f"""from_iso8601_timestamp('{datetime_formatted}')"""
return None
@classmethod
def epoch_to_dttm(cls) -> str:
return "from_unixtime({col})"
@staticmethod
def _mutate_label(label: str) -> str:
"""
Athena only supports lowercase column names and aliases.
:param label: Expected expression label
:return: Conditionally mutated label
"""
return label.lower()
class BigQueryEngineSpec(BaseEngineSpec):
"""Engine spec for Google's BigQuery
As contributed by @mxmzdlv on issue #945"""
engine = "bigquery"
engine_name = "Google BigQuery"
max_column_name_length = 128
parameters_schema = BigQueryParametersSchema()
default_driver = "bigquery"
sqlalchemy_uri_placeholder = "bigquery://{project_id}"
# BigQuery doesn't maintain context when running multiple statements in the
# same cursor, so we need to run all statements at once
run_multiple_statements_as_one = True
"""
https://www.python.org/dev/peps/pep-0249/#arraysize
raw_connections bypass the pybigquery query execution context and deal with
raw dbapi connection directly.
If this value is not set, the default value is set to 1, as described here,
https://googlecloudplatform.github.io/google-cloud-python/latest/_modules/google/cloud/bigquery/dbapi/cursor.html#Cursor
The default value of 5000 is derived from the pybigquery.
https://github.com/mxmzdlv/pybigquery/blob/d214bb089ca0807ca9aaa6ce4d5a01172d40264e/pybigquery/sqlalchemy_bigquery.py#L102
"""
arraysize = 5000
_date_trunc_functions = {
"DATE": "DATE_TRUNC",
"DATETIME": "DATETIME_TRUNC",
"TIME": "TIME_TRUNC",
"TIMESTAMP": "TIMESTAMP_TRUNC",
}
_time_grain_expressions = {
None: "{col}",
"PT1S": "{func}({col}, SECOND)",
"PT1M": "{func}({col}, MINUTE)",
"PT5M": "CAST(TIMESTAMP_SECONDS("
"5*60 * DIV(UNIX_SECONDS(CAST({col} AS TIMESTAMP)), 5*60)"
") AS {type})",
"PT10M": "CAST(TIMESTAMP_SECONDS("
"10*60 * DIV(UNIX_SECONDS(CAST({col} AS TIMESTAMP)), 10*60)"
") AS {type})",
"PT15M": "CAST(TIMESTAMP_SECONDS("
"15*60 * DIV(UNIX_SECONDS(CAST({col} AS TIMESTAMP)), 15*60)"
") AS {type})",
"PT0.5H": "CAST(TIMESTAMP_SECONDS("
"30*60 * DIV(UNIX_SECONDS(CAST({col} AS TIMESTAMP)), 30*60)"
") AS {type})",
"PT1H": "{func}({col}, HOUR)",
"P1D": "{func}({col}, DAY)",
"P1W": "{func}({col}, WEEK)",
"P1M": "{func}({col}, MONTH)",
"P0.25Y": "{func}({col}, QUARTER)",
"P1Y": "{func}({col}, YEAR)",
}
custom_errors: Dict[Pattern[str], Tuple[str, SupersetErrorType, Dict[str, Any]]] = {
CONNECTION_DATABASE_PERMISSIONS_REGEX: (
__(
"We were unable to connect to your database. Please "
"confirm that your service account has the Viewer "
"and Job User roles on the project."
),
SupersetErrorType.CONNECTION_DATABASE_PERMISSIONS_ERROR,
{},
),
TABLE_DOES_NOT_EXIST_REGEX: (
__(
'The table "%(table)s" does not exist. '
"A valid table must be used to run this query.",
),
SupersetErrorType.TABLE_DOES_NOT_EXIST_ERROR,
{},
),
COLUMN_DOES_NOT_EXIST_REGEX: (
__('We can\'t seem to resolve column "%(column)s" at line %(location)s.'),
SupersetErrorType.COLUMN_DOES_NOT_EXIST_ERROR,
{},
),
SCHEMA_DOES_NOT_EXIST_REGEX: (
__(
'The schema "%(schema)s" does not exist. '
"A valid schema must be used to run this query."
),
SupersetErrorType.SCHEMA_DOES_NOT_EXIST_ERROR,
{},
),
SYNTAX_ERROR_REGEX: (
__(
"Please check your query for syntax errors at or near "
'"%(syntax_error)s". Then, try running your query again.'
),
SupersetErrorType.SYNTAX_ERROR,
{},
),
}
@classmethod
def convert_dttm(cls, target_type: str, dttm: datetime) -> Optional[str]:
tt = target_type.upper()
if tt == utils.TemporalType.DATE:
return f"CAST('{dttm.date().isoformat()}' AS DATE)"
if tt == utils.TemporalType.DATETIME:
return f"""CAST('{dttm.isoformat(timespec="microseconds")}' AS DATETIME)"""
if tt == utils.TemporalType.TIME:
return f"""CAST('{dttm.strftime("%H:%M:%S.%f")}' AS TIME)"""
if tt == utils.TemporalType.TIMESTAMP:
return f"""CAST('{dttm.isoformat(timespec="microseconds")}' AS TIMESTAMP)"""
return None
@classmethod
def fetch_data(
cls, cursor: Any, limit: Optional[int] = None
) -> List[Tuple[Any, ...]]:
data = super().fetch_data(cursor, limit)
# Support type BigQuery Row, introduced here PR #4071
# google.cloud.bigquery.table.Row
if data and type(data[0]).__name__ == "Row":
data = [r.values() for r in data] # type: ignore
return data
@staticmethod
def _mutate_label(label: str) -> str:
"""
BigQuery field_name should start with a letter or underscore and contain only
alphanumeric characters. Labels that start with a number are prefixed with an
underscore. Any unsupported characters are replaced with underscores and an
md5 hash is added to the end of the label to avoid possible collisions.
:param label: Expected expression label
:return: Conditionally mutated label
"""
label_hashed = "_" + md5_sha_from_str(label)
# if label starts with number, add underscore as first character
label_mutated = "_" + label if re.match(r"^\d", label) else label
# replace non-alphanumeric characters with underscores
label_mutated = re.sub(r"[^\w]+", "_", label_mutated)
if label_mutated != label:
# add first 5 chars from md5 hash to label to avoid possible collisions
label_mutated += label_hashed[:6]
return label_mutated
@classmethod
def _truncate_label(cls, label: str) -> str:
"""BigQuery requires column names start with either a letter or
underscore. To make sure this is always the case, an underscore is prefixed
to the md5 hash of the original label.
:param label: expected expression label
:return: truncated label
"""
return "_" + md5_sha_from_str(label)
@classmethod
def normalize_indexes(cls, indexes: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
"""
Normalizes indexes for more consistency across db engines
:param indexes: Raw indexes as returned by SQLAlchemy
:return: cleaner, more aligned index definition
"""
normalized_idxs = []
# Fixing a bug/behavior observed in pybigquery==0.4.15 where
# the index's `column_names` == [None]
# Here we're returning only non-None indexes
for ix in indexes:
column_names = ix.get("column_names") or []
ix["column_names"] = [col for col in column_names if col is not None]
if ix["column_names"]:
normalized_idxs.append(ix)
return normalized_idxs
@classmethod
def extra_table_metadata(
cls, database: "Database", table_name: str, schema_name: str
) -> Dict[str, Any]:
indexes = database.get_indexes(table_name, schema_name)
if not indexes:
return {}
partitions_columns = [
index.get("column_names", [])
for index in indexes
if index.get("name") == "partition"
]
cluster_columns = [
index.get("column_names", [])
for index in indexes
if index.get("name") == "clustering"
]
return {
"partitions": {"cols": partitions_columns},
"clustering": {"cols": cluster_columns},
}
@classmethod
def _get_fields(cls, cols: List[Dict[str, Any]]) -> List[ColumnClause]:
"""
BigQuery dialect requires us to not use backtick in the fieldname which are
nested.
Using literal_column handles that issue.
https://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-more-specific-text-with-table-literal-column-and-column
Also explicility specifying column names so we don't encounter duplicate
column names in the result.
"""
return [
literal_column(c["name"]).label(c["name"].replace(".", "__")) for c in cols
]
@classmethod
def epoch_to_dttm(cls) -> str:
return "TIMESTAMP_SECONDS({col})"
@classmethod
def epoch_ms_to_dttm(cls) -> str:
return "TIMESTAMP_MILLIS({col})"
@classmethod
def df_to_sql(
cls,
database: "Database",
table: Table,
df: pd.DataFrame,
to_sql_kwargs: Dict[str, Any],
) -> None:
"""
Upload data from a Pandas DataFrame to a database.
Calls `pandas_gbq.DataFrame.to_gbq` which requires `pandas_gbq` to be installed.
Note this method does not create metadata for the table.
:param database: The database to upload the data to
:param table: The table to upload the data to
:param df: The dataframe with data to be uploaded
:param to_sql_kwargs: The kwargs to be passed to pandas.DataFrame.to_sql` method
"""
try:
import pandas_gbq
from google.oauth2 import service_account
except ImportError:
raise Exception(
"Could not import libraries `pandas_gbq` or `google.oauth2`, which are "
"required to be installed in your environment in order "
"to upload data to BigQuery"
)
if not table.schema:
raise Exception("The table schema must be defined")
engine = cls.get_engine(database)
to_gbq_kwargs = {"destination_table": str(table), "project_id": engine.url.host}
# Add credentials if they are set on the SQLAlchemy dialect.
creds = engine.dialect.credentials_info
if creds:
to_gbq_kwargs[
"credentials"
] = service_account.Credentials.from_service_account_info(creds)
# Only pass through supported kwargs.
supported_kwarg_keys = {"if_exists"}
for key in supported_kwarg_keys:
if key in to_sql_kwargs:
to_gbq_kwargs[key] = to_sql_kwargs[key]
pandas_gbq.to_gbq(df, **to_gbq_kwargs)
@classmethod
def build_sqlalchemy_uri(
cls, _: BigQueryParametersType, encrypted_extra: Optional[Dict[str, Any]] = None
) -> str:
if encrypted_extra:
project_id = encrypted_extra.get("credentials_info", {}).get("project_id")
if project_id:
return f"{cls.engine}+{cls.default_driver}://{project_id}"
raise SupersetGenericDBErrorException(
message="Big Query encrypted_extra is not available.",
)
@classmethod
def get_parameters_from_uri(
cls, _: str, encrypted_extra: Optional[Dict[str, str]] = None
) -> Any:
# BigQuery doesn't have parameters
if encrypted_extra:
return encrypted_extra
raise SupersetGenericDBErrorException(
message="Big Query encrypted_extra is not available.",
)
@classmethod
def validate_parameters(
cls, parameters: BigQueryParametersType # pylint: disable=unused-argument
) -> List[SupersetError]:
return []
@classmethod
def parameters_json_schema(cls) -> Any:
"""
Return configuration parameters as OpenAPI.
"""
if not cls.parameters_schema:
return None
spec = APISpec(
title="Database Parameters",
version="1.0.0",
openapi_version="3.0.0",
plugins=[ma_plugin],
)
ma_plugin.init_spec(spec)
ma_plugin.converter.add_attribute_function(encrypted_field_properties)
spec.components.schema(cls.__name__, schema=cls.parameters_schema)
return spec.to_dict()["components"]["schemas"][cls.__name__]
class QueryView(SupersetModelView):
datamodel = SQLAInterface(Query)
list_columns = ['user', 'database', 'status', 'start_time', 'end_time']
label_columns = {
'user': _('User'),
'database': _('Database'),
'status': _('Status'),
'start_time': _('Start Time'),
'end_time': _('End Time'),
}
appbuilder.add_view(
QueryView,
"Queries",
label=__("Queries"),
category="Manage",
category_label=__("Manage"),
icon="fa-search")
class SavedQueryView(SupersetModelView, DeleteMixin):
datamodel = SQLAInterface(SavedQuery)
list_title = _('List Saved Query')
show_title = _('Show Saved Query')
add_title = _('Add Saved Query')
edit_title = _('Edit Saved Query')
list_columns = [
'label', 'user', 'database', 'schema', 'description',
class DeleteMixin(object):
def _delete(self, pk):
"""
Delete function logic, override to implement diferent logic
deletes the record with primary_key = pk
:param pk:
record primary key to delete
"""
item = self.datamodel.get(pk, self._base_filters)
if not item:
abort(404)
try:
self.pre_delete(item)
except Exception as e:
flash(str(e), 'danger')
else:
view_menu = security_manager.find_view_menu(item.get_perm())
pvs = security_manager.get_session.query(
security_manager.permissionview_model).filter_by(
view_menu=view_menu).all()
schema_view_menu = None
if hasattr(item, 'schema_perm'):
schema_view_menu = security_manager.find_view_menu(item.schema_perm)
pvs.extend(security_manager.get_session.query(
security_manager.permissionview_model).filter_by(
view_menu=schema_view_menu).all())
if self.datamodel.delete(item):
self.post_delete(item)
for pv in pvs:
security_manager.get_session.delete(pv)
if view_menu:
security_manager.get_session.delete(view_menu)
if schema_view_menu:
security_manager.get_session.delete(schema_view_menu)
security_manager.get_session.commit()
flash(*self.datamodel.message)
self.update_redirect()
@action(
'muldelete',
__('Delete'),
__('Delete all Really?'),
'fa-trash',
single=False,
)
def muldelete(self, items):
if not items:
abort(404)
for item in items:
try:
self.pre_delete(item)
except Exception as e:
flash(str(e), 'danger')
else:
self._delete(item.id)
self.update_redirect()
return redirect(self.get_redirect())
def get_datasource_exist_error_mgs(full_name):
return __("Datasource %(name)s already exists", name=full_name)
def execute_sql_statement( # pylint: disable=too-many-arguments,too-many-locals
sql_statement: str,
query: Query,
user_name: Optional[str],
session: Session,
cursor: Any,
log_params: Optional[Dict[str, Any]],
apply_ctas: bool = False,
) -> SupersetResultSet:
"""Executes a single SQL statement"""
database = query.database
db_engine_spec = database.db_engine_spec
parsed_query = ParsedQuery(sql_statement)
sql = parsed_query.stripped()
# This is a test to see if the query is being
# limited by either the dropdown or the sql.
# We are testing to see if more rows exist than the limit.
increased_limit = None if query.limit is None else query.limit + 1
if not db_engine_spec.is_readonly_query(parsed_query) and not database.allow_dml:
raise SupersetErrorException(
SupersetError(
message=__("Only SELECT statements are allowed against this database."),
error_type=SupersetErrorType.DML_NOT_ALLOWED_ERROR,
level=ErrorLevel.ERROR,
)
)
if apply_ctas:
if not query.tmp_table_name:
start_dttm = datetime.fromtimestamp(query.start_time)
query.tmp_table_name = "tmp_{}_table_{}".format(
query.user_id, start_dttm.strftime("%Y_%m_%d_%H_%M_%S")
)
sql = parsed_query.as_create_table(
query.tmp_table_name,
schema_name=query.tmp_schema_name,
method=query.ctas_method,
)
query.select_as_cta_used = True
# Do not apply limit to the CTA queries when SQLLAB_CTAS_NO_LIMIT is set to true
if db_engine_spec.is_select_query(parsed_query) and not (
query.select_as_cta_used and SQLLAB_CTAS_NO_LIMIT
):
if SQL_MAX_ROW and (not query.limit or query.limit > SQL_MAX_ROW):
query.limit = SQL_MAX_ROW
sql = apply_limit_if_exists(database, increased_limit, query, sql)
# Hook to allow environment-specific mutation (usually comments) to the SQL
sql = SQL_QUERY_MUTATOR(sql, user_name, security_manager, database)
try:
query.executed_sql = sql
if log_query:
log_query(
query.database.sqlalchemy_uri,
query.executed_sql,
query.schema,
user_name,
__name__,
security_manager,
log_params,
)
session.commit()
with stats_timing("sqllab.query.time_executing_query", stats_logger):
logger.debug("Query %d: Running query: %s", query.id, sql)
db_engine_spec.execute(cursor, sql, async_=True)
logger.debug("Query %d: Handling cursor", query.id)
db_engine_spec.handle_cursor(cursor, query, session)
with stats_timing("sqllab.query.time_fetching_results", stats_logger):
logger.debug(
"Query %d: Fetching data for query object: %s",
query.id,
str(query.to_dict()),
)
data = db_engine_spec.fetch_data(cursor, increased_limit)
if query.limit is None or len(data) <= query.limit:
query.limiting_factor = LimitingFactor.NOT_LIMITED
else:
# return 1 row less than increased_query
data = data[:-1]
except SoftTimeLimitExceeded as ex:
logger.warning("Query %d: Time limit exceeded", query.id)
logger.debug("Query %d: %s", query.id, ex)
raise SupersetErrorException(
SupersetError(
message=__(
"The query was killed after %(sqllab_timeout)s seconds. It might "
"be too complex, or the database might be under heavy load.",
sqllab_timeout=SQLLAB_TIMEOUT,
),
error_type=SupersetErrorType.SQLLAB_TIMEOUT_ERROR,
level=ErrorLevel.ERROR,
)
) from ex
except Exception as ex:
# query is stopped in another thread/worker
# stopping raises expected exceptions which we should skip
session.refresh(query)
if query.status == QueryStatus.STOPPED:
raise SqlLabQueryStoppedException() from ex
logger.error("Query %d: %s", query.id, type(ex), exc_info=True)
logger.debug("Query %d: %s", query.id, ex)
raise SqlLabException(db_engine_spec.extract_error_message(ex)) from ex
logger.debug("Query %d: Fetching cursor description", query.id)
cursor_description = cursor.description
return SupersetResultSet(data, cursor_description, db_engine_spec)
def get_datasource_exist_error_msg(full_name):
return __('Datasource %(name)s already exists', name=full_name)
obj.end_dttm = obj.start_dttm
elif obj.end_dttm < obj.start_dttm:
raise Exception(
"Annotation end time must be no earlier than start time.")
def pre_update(self, obj):
self.pre_add(obj)
class AnnotationLayerModelView(SupersetModelView, DeleteMixin):
datamodel = SQLAInterface(AnnotationLayer)
list_columns = ['id', 'name']
edit_columns = ['name', 'descr']
add_columns = edit_columns
appbuilder.add_view(AnnotationLayerModelView,
"Annotation Layers",
label=__("Annotation Layers"),
icon="fa-comment",
category="Manage",
category_label=__("Manage"),
category_icon='')
appbuilder.add_view(AnnotationModelView,
"Annotations",
label=__("Annotations"),
icon="fa-comments",
category="Manage",
category_label=__("Manage"),
category_icon='')
def deliver_dashboard(
dashboard_id: int,
recipients: Optional[str],
slack_channel: Optional[str],
delivery_type: EmailDeliveryType,
deliver_as_group: bool,
) -> None:
"""
Given a schedule, delivery the dashboard as an email report
"""
dashboard = db.session.query(Dashboard).filter_by(id=dashboard_id).one()
dashboard_url = _get_url_path("Superset.dashboard",
dashboard_id_or_slug=dashboard.id)
dashboard_url_user_friendly = _get_url_path(
"Superset.dashboard",
user_friendly=True,
dashboard_id_or_slug=dashboard.id)
# Create a driver, fetch the page, wait for the page to render
driver = create_webdriver()
window = config["WEBDRIVER_WINDOW"]["dashboard"]
driver.set_window_size(*window)
driver.get(dashboard_url)
time.sleep(EMAIL_PAGE_RENDER_WAIT)
# Set up a function to retry once for the element.
# This is buggy in certain selenium versions with firefox driver
get_element = getattr(driver, "find_element_by_class_name")
element = retry_call(get_element,
fargs=["grid-container"],
tries=2,
delay=EMAIL_PAGE_RENDER_WAIT)
try:
screenshot = element.screenshot_as_png
except WebDriverException:
# Some webdrivers do not support screenshots for elements.
# In such cases, take a screenshot of the entire page.
screenshot = driver.screenshot() # pylint: disable=no-member
finally:
destroy_webdriver(driver)
# Generate the email body and attachments
report_content = _generate_report_content(
delivery_type,
screenshot,
dashboard.dashboard_title,
dashboard_url_user_friendly,
)
subject = __(
"%(prefix)s %(title)s",
prefix=config["EMAIL_REPORTS_SUBJECT_PREFIX"],
title=dashboard.dashboard_title,
)
if recipients:
_deliver_email(
recipients,
deliver_as_group,
subject,
report_content.body,
report_content.data,
report_content.images,
)
if slack_channel:
deliver_slack_msg(
slack_channel,
subject,
report_content.slack_message,
report_content.slack_attachment,
)
}
def pre_add(self, cluster):
security_manager.merge_perm('database_access', cluster.perm)
def pre_update(self, cluster):
self.pre_add(cluster)
def _delete(self, pk):
DeleteMixin._delete(self, pk)
appbuilder.add_view(
DruidClusterModelView,
name='Druid Clusters',
label=__('Druid Clusters'),
icon='fa-cubes',
category='Sources',
category_label=__('Sources'),
category_icon='fa-database',
)
class DruidDatasourceModelView(DatasourceModelView, DeleteMixin,
YamlExportMixin): # noqa
datamodel = SQLAInterface(models.DruidDatasource)
list_title = _('List Druid Datasource')
show_title = _('Show Druid Datasource')
add_title = _('Add Druid Datasource')
edit_title = _('Edit Druid Datasource')
security.merge_perm(sm, 'schema_access', table.schema_perm)
if flash_message:
flash(_(
"The table was created. "
"As part of this two phase configuration "
"process, you should now click the edit button by "
"the new table to configure it."), "info")
def post_update(self, table):
self.post_add(table, flash_message=False)
@expose('/edit/<pk>', methods=['GET', 'POST'])
@has_access
def edit(self, pk):
"""Simple hack to redirect to explore view after saving"""
resp = super(TableModelView, self).edit(pk)
if isinstance(resp, basestring):
return resp
return redirect('/superset/explore/table/{}/'.format(pk))
appbuilder.add_view(
TableModelView,
"Tables",
label=__("Tables"),
category="Sources",
category_label=__("Sources"),
icon='fa-table',)
appbuilder.add_separator("Sources")
def _get_slice_data(slc: Slice,
delivery_type: EmailDeliveryType) -> ReportContent:
slice_url = _get_url_path("Superset.explore_json",
csv="true",
form_data=json.dumps({"slice_id": slc.id}))
# URL to include in the email
slice_url_user_friendly = _get_url_path("Superset.slice",
slice_id=slc.id,
user_friendly=True)
# Login on behalf of the "reports" user in order to get cookies to deal with auth
auth_cookies = machine_auth_provider_factory.instance.get_auth_cookies(
get_reports_user())
# Build something like "session=cool_sess.val;other-cookie=awesome_other_cookie"
cookie_str = ";".join(
[f"{key}={val}" for key, val in auth_cookies.items()])
opener = urllib.request.build_opener()
opener.addheaders.append(("Cookie", cookie_str))
response = opener.open(slice_url)
if response.getcode() != 200:
raise URLError(response.getcode())
# TODO: Move to the csv module
content = response.read()
rows = [r.split(b",") for r in content.splitlines()]
if delivery_type == EmailDeliveryType.inline:
data = None
# Parse the csv file and generate HTML
columns = rows.pop(0)
with app.app_context(): # type: ignore
body = render_template(
"superset/reports/slice_data.html",
columns=columns,
rows=rows,
name=slc.slice_name,
link=slice_url_user_friendly,
)
elif delivery_type == EmailDeliveryType.attachment:
data = {__("%(name)s.csv", name=slc.slice_name): content}
body = __(
'<b><a href="%(url)s">Explore in Superset</a></b><p></p>',
name=slc.slice_name,
url=slice_url_user_friendly,
)
# how to: https://api.slack.com/reference/surfaces/formatting
slack_message = __(
"""
*%(slice_name)s*\n
<%(slice_url_user_friendly)s|Explore in Superset>
""",
slice_name=slc.slice_name,
slice_url_user_friendly=slice_url_user_friendly,
)
return ReportContent(body, data, None, slack_message, content)
t.fetch_metadata()
successes.append(t)
except Exception:
failures.append(t)
if len(successes) > 0:
success_msg = _(
'Metadata refreshed for the following table(s): %(tables)s',
tables=', '.join([t.table_name for t in successes]))
flash(success_msg, 'info')
if len(failures) > 0:
failure_msg = _(
'Unable to retrieve metadata for the following table(s): %(tables)s',
tables=', '.join([t.table_name for t in failures]))
flash(failure_msg, 'danger')
return redirect('/tablemodelview/list/')
appbuilder.add_view_no_menu(TableModelView)
appbuilder.add_link(
'Tables',
label=__('Tables'),
href='/tablemodelview/list/?_flt_1_is_sqllab_view=y',
icon='fa-table',
category='Sources',
category_label=__('Sources'),
category_icon='fa-table')
appbuilder.add_separator('Sources')
class DashboardModelView(DashboardMixin, SupersetModelView, DeleteMixin): # pylint: disable=too-many-ancestors
route_base = "/dashboard"
datamodel = SQLAInterface(models.Dashboard)
# TODO disable api_read and api_delete (used by cypress)
# once we move to ChartRestModelApi
include_route_methods = RouteMethod.CRUD_SET | {
RouteMethod.API_READ,
RouteMethod.API_DELETE,
"download_dashboards",
}
@has_access
@expose("/list/")
def list(self):
if not app.config["ENABLE_REACT_CRUD_VIEWS"]:
return super().list()
payload = {
"user": bootstrap_user_data(g.user),
"common": common_bootstrap_payload(),
}
return self.render_template(
"superset/welcome.html",
entry="welcome",
bootstrap_data=json.dumps(
payload, default=utils.pessimistic_json_iso_dttm_ser),
)
@action("mulexport", __("Export"), __("Export dashboards?"), "fa-database")
def mulexport(self, items): # pylint: disable=no-self-use
if not isinstance(items, list):
items = [items]
ids = "".join("&id={}".format(d.id) for d in items)
return redirect("/dashboard/export_dashboards_form?{}".format(ids[1:]))
@event_logger.log_this
@has_access
@expose("/export_dashboards_form")
def download_dashboards(self):
if request.args.get("action") == "go":
ids = request.args.getlist("id")
return Response(
models.Dashboard.export_dashboards(ids),
headers=generate_download_headers("json"),
mimetype="application/text",
)
return self.render_template("superset/export_dashboards.html",
dashboards_url="/dashboard/list")
def pre_add(self, item):
item.slug = item.slug or None
if item.slug:
item.slug = item.slug.strip()
item.slug = item.slug.replace(" ", "-")
item.slug = re.sub(r"[^\w\-]+", "", item.slug)
if g.user not in item.owners:
item.owners.append(g.user)
utils.validate_json(item.json_metadata)
utils.validate_json(item.position_json)
owners = [o for o in item.owners]
for slc in item.slices:
slc.owners = list(set(owners) | set(slc.owners))
def pre_update(self, item):
check_ownership(item)
self.pre_add(item)
def explore(self, datasource_type, datasource_id):
error_redirect = '/slicemodelview/list/'
datasource_class = models.SqlaTable \
if datasource_type == "table" else models.DruidDatasource
datasources = (
db.session
.query(datasource_class)
.all()
)
datasources = sorted(datasources, key=lambda ds: ds.full_name)
datasource = [ds for ds in datasources if int(datasource_id) == ds.id]
datasource = datasource[0] if datasource else None
slice_id = request.args.get("slice_id")
slc = None
if slice_id:
slc = (
db.session.query(models.Slice)
.filter_by(id=slice_id)
.first()
)
if not datasource:
flash(__("The datasource seems to have been deleted"), "alert")
return redirect(error_redirect)
slice_add_perm = self.can_access('can_add', 'SliceModelView')
slice_edit_perm = check_ownership(slc, raise_if_false=False)
slice_download_perm = self.can_access('can_download', 'SliceModelView')
all_datasource_access = self.can_access(
'all_datasource_access', 'all_datasource_access')
datasource_access = self.can_access(
'datasource_access', datasource.perm)
if not (all_datasource_access or datasource_access):
flash(__("You don't seem to have access to this datasource"), "danger")
return redirect(error_redirect)
action = request.args.get('action')
if action in ('saveas', 'overwrite'):
return self.save_or_overwrite_slice(
request.args, slc, slice_add_perm, slice_edit_perm)
viz_type = request.args.get("viz_type")
if not viz_type and datasource.default_endpoint:
return redirect(datasource.default_endpoint)
if not viz_type:
viz_type = "table"
try:
obj = viz.viz_types[viz_type](
datasource,
form_data=request.args,
slice_=slc)
except Exception as e:
flash(str(e), "danger")
return redirect(error_redirect)
if request.args.get("json") == "true":
status = 200
if config.get("DEBUG"):
# Allows for nice debugger stack traces in debug mode
payload = obj.get_json()
else:
try:
payload = obj.get_json()
except Exception as e:
logging.exception(e)
payload = str(e)
status = 500
resp = Response(
payload,
status=status,
mimetype="application/json")
return resp
elif request.args.get("csv") == "true":
payload = obj.get_csv()
return Response(
payload,
status=200,
headers=generate_download_headers("csv"),
mimetype="application/csv")
else:
if request.args.get("standalone") == "true":
template = "caravel/standalone.html"
else:
template = "caravel/explore.html"
resp = self.render_template(
template, viz=obj, slice=slc, datasources=datasources,
can_add=slice_add_perm, can_edit=slice_edit_perm,
can_download=slice_download_perm,
userid=g.user.get_id() if g.user else '')
try:
pass
except Exception as e:
if config.get("DEBUG"):
raise(e)
return Response(
str(e),
status=500,
mimetype="application/json")
return resp
class BigQueryEngineSpec(BaseEngineSpec):
"""Engine spec for Google's BigQuery
As contributed by @mxmzdlv on issue #945"""
engine = "bigquery"
engine_name = "Google BigQuery"
max_column_name_length = 128
# BigQuery doesn't maintain context when running multiple statements in the
# same cursor, so we need to run all statements at once
run_multiple_statements_as_one = True
"""
https://www.python.org/dev/peps/pep-0249/#arraysize
raw_connections bypass the pybigquery query execution context and deal with
raw dbapi connection directly.
If this value is not set, the default value is set to 1, as described here,
https://googlecloudplatform.github.io/google-cloud-python/latest/_modules/google/cloud/bigquery/dbapi/cursor.html#Cursor
The default value of 5000 is derived from the pybigquery.
https://github.com/mxmzdlv/pybigquery/blob/d214bb089ca0807ca9aaa6ce4d5a01172d40264e/pybigquery/sqlalchemy_bigquery.py#L102
"""
arraysize = 5000
_date_trunc_functions = {
"DATE": "DATE_TRUNC",
"DATETIME": "DATETIME_TRUNC",
"TIME": "TIME_TRUNC",
"TIMESTAMP": "TIMESTAMP_TRUNC",
}
_time_grain_expressions = {
None:
"{col}",
"PT1S":
"{func}({col}, SECOND)",
"PT1M":
"{func}({col}, MINUTE)",
"PT5M":
"CAST(TIMESTAMP_SECONDS("
"5*60 * DIV(UNIX_SECONDS(CAST({col} AS TIMESTAMP)), 5*60)"
") AS {type})",
"PT10M":
"CAST(TIMESTAMP_SECONDS("
"10*60 * DIV(UNIX_SECONDS(CAST({col} AS TIMESTAMP)), 10*60)"
") AS {type})",
"PT15M":
"CAST(TIMESTAMP_SECONDS("
"15*60 * DIV(UNIX_SECONDS(CAST({col} AS TIMESTAMP)), 15*60)"
") AS {type})",
"PT0.5H":
"CAST(TIMESTAMP_SECONDS("
"30*60 * DIV(UNIX_SECONDS(CAST({col} AS TIMESTAMP)), 30*60)"
") AS {type})",
"PT1H":
"{func}({col}, HOUR)",
"P1D":
"{func}({col}, DAY)",
"P1W":
"{func}({col}, WEEK)",
"P1M":
"{func}({col}, MONTH)",
"P0.25Y":
"{func}({col}, QUARTER)",
"P1Y":
"{func}({col}, YEAR)",
}
custom_errors = {
CONNECTION_DATABASE_PERMISSIONS_REGEX: (
__("We were unable to connect to your database. Please "
"confirm that your service account has the Viewer "
"and Job User roles on the project."),
SupersetErrorType.CONNECTION_DATABASE_PERMISSIONS_ERROR,
),
}
@classmethod
def convert_dttm(cls, target_type: str, dttm: datetime) -> Optional[str]:
tt = target_type.upper()
if tt == utils.TemporalType.DATE:
return f"CAST('{dttm.date().isoformat()}' AS DATE)"
if tt == utils.TemporalType.DATETIME:
return f"""CAST('{dttm.isoformat(timespec="microseconds")}' AS DATETIME)"""
if tt == utils.TemporalType.TIME:
return f"""CAST('{dttm.strftime("%H:%M:%S.%f")}' AS TIME)"""
if tt == utils.TemporalType.TIMESTAMP:
return f"""CAST('{dttm.isoformat(timespec="microseconds")}' AS TIMESTAMP)"""
return None
@classmethod
def fetch_data(cls,
cursor: Any,
limit: Optional[int] = None) -> List[Tuple[Any, ...]]:
data = super().fetch_data(cursor, limit)
# Support type BigQuery Row, introduced here PR #4071
# google.cloud.bigquery.table.Row
if data and type(data[0]).__name__ == "Row":
data = [r.values() for r in data] # type: ignore
return data
@staticmethod
def _mutate_label(label: str) -> str:
"""
BigQuery field_name should start with a letter or underscore and contain only
alphanumeric characters. Labels that start with a number are prefixed with an
underscore. Any unsupported characters are replaced with underscores and an
md5 hash is added to the end of the label to avoid possible collisions.
:param label: Expected expression label
:return: Conditionally mutated label
"""
label_hashed = "_" + hashlib.md5(label.encode("utf-8")).hexdigest()
# if label starts with number, add underscore as first character
label_mutated = "_" + label if re.match(r"^\d", label) else label
# replace non-alphanumeric characters with underscores
label_mutated = re.sub(r"[^\w]+", "_", label_mutated)
if label_mutated != label:
# add first 5 chars from md5 hash to label to avoid possible collisions
label_mutated += label_hashed[:6]
return label_mutated
@classmethod
def _truncate_label(cls, label: str) -> str:
"""BigQuery requires column names start with either a letter or
underscore. To make sure this is always the case, an underscore is prefixed
to the md5 hash of the original label.
:param label: expected expression label
:return: truncated label
"""
return "_" + hashlib.md5(label.encode("utf-8")).hexdigest()
@classmethod
def normalize_indexes(
cls, indexes: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
"""
Normalizes indexes for more consistency across db engines
:param indexes: Raw indexes as returned by SQLAlchemy
:return: cleaner, more aligned index definition
"""
normalized_idxs = []
# Fixing a bug/behavior observed in pybigquery==0.4.15 where
# the index's `column_names` == [None]
# Here we're returning only non-None indexes
for ix in indexes:
column_names = ix.get("column_names") or []
ix["column_names"] = [
col for col in column_names if col is not None
]
if ix["column_names"]:
normalized_idxs.append(ix)
return normalized_idxs
@classmethod
def extra_table_metadata(cls, database: "Database", table_name: str,
schema_name: str) -> Dict[str, Any]:
indexes = database.get_indexes(table_name, schema_name)
if not indexes:
return {}
partitions_columns = [
index.get("column_names", []) for index in indexes
if index.get("name") == "partition"
]
cluster_columns = [
index.get("column_names", []) for index in indexes
if index.get("name") == "clustering"
]
return {
"partitions": {
"cols": partitions_columns
},
"clustering": {
"cols": cluster_columns
},
}
@classmethod
def _get_fields(cls, cols: List[Dict[str, Any]]) -> List[ColumnClause]:
"""
BigQuery dialect requires us to not use backtick in the fieldname which are
nested.
Using literal_column handles that issue.
https://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-more-specific-text-with-table-literal-column-and-column
Also explicility specifying column names so we don't encounter duplicate
column names in the result.
"""
return [
literal_column(c["name"]).label(c["name"].replace(".", "__"))
for c in cols
]
@classmethod
def epoch_to_dttm(cls) -> str:
return "TIMESTAMP_SECONDS({col})"
@classmethod
def epoch_ms_to_dttm(cls) -> str:
return "TIMESTAMP_MILLIS({col})"
@classmethod
def df_to_sql(cls, df: pd.DataFrame, **kwargs: Any) -> None:
"""
Upload data from a Pandas DataFrame to BigQuery. Calls
`DataFrame.to_gbq()` which requires `pandas_gbq` to be installed.
:param df: Dataframe with data to be uploaded
:param kwargs: kwargs to be passed to to_gbq() method. Requires that `schema`,
`name` and `con` are present in kwargs. `name` and `schema` are combined
and passed to `to_gbq()` as `destination_table`.
"""
try:
import pandas_gbq
from google.oauth2 import service_account
except ImportError:
raise Exception(
"Could not import libraries `pandas_gbq` or `google.oauth2`, which are "
"required to be installed in your environment in order "
"to upload data to BigQuery")
if not ("name" in kwargs and "schema" in kwargs and "con" in kwargs):
raise Exception(
"name, schema and con need to be defined in kwargs")
gbq_kwargs = {}
gbq_kwargs["project_id"] = kwargs["con"].engine.url.host
gbq_kwargs[
"destination_table"] = f"{kwargs.pop('schema')}.{kwargs.pop('name')}"
# add credentials if they are set on the SQLAlchemy Dialect:
creds = kwargs["con"].dialect.credentials_info
if creds:
credentials = service_account.Credentials.from_service_account_info(
creds)
gbq_kwargs["credentials"] = credentials
# Only pass through supported kwargs
supported_kwarg_keys = {"if_exists"}
for key in supported_kwarg_keys:
if key in kwargs:
gbq_kwargs[key] = kwargs[key]
pandas_gbq.to_gbq(df, **gbq_kwargs)
