def validate_parameters(
cls,
parameters: GSheetsParametersType,
) -> List[SupersetError]:
errors: List[SupersetError] = []
encrypted_credentials = parameters.get("service_account_info") or "{}"
# On create the encrypted credentials are a string,
# at all other times they are a dict
if isinstance(encrypted_credentials, str):
encrypted_credentials = json.loads(encrypted_credentials)
table_catalog = parameters.get("catalog", {})
if not table_catalog:
# Allowing users to submit empty catalogs
return errors
# We need a subject in case domain wide delegation is set, otherwise the
# check will fail. This means that the admin will be able to add sheets
# that only they have access, even if later users are not able to access
# them.
subject = g.user.email if g.user else None
engine = create_engine(
"gsheets://",
service_account_info=encrypted_credentials,
subject=subject,
)
conn = engine.connect()
idx = 0
for name, url in table_catalog.items():
if not name:
errors.append(
SupersetError(
message="Sheet name is required",
error_type=SupersetErrorType.
CONNECTION_MISSING_PARAMETERS_ERROR,
level=ErrorLevel.WARNING,
extra={"catalog": {
"idx": idx,
"name": True
}},
), )
return errors
if not url:
errors.append(
SupersetError(
message="URL is required",
error_type=SupersetErrorType.
CONNECTION_MISSING_PARAMETERS_ERROR,
level=ErrorLevel.WARNING,
extra={"catalog": {
"idx": idx,
"url": True
}},
), )
return errors
try:
results = conn.execute(f'SELECT * FROM "{url}" LIMIT 1')
results.fetchall()
except Exception: # pylint: disable=broad-except
errors.append(
SupersetError(
message="URL could not be identified",
error_type=SupersetErrorType.
TABLE_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.WARNING,
extra={"catalog": {
"idx": idx,
"url": True
}},
), )
idx += 1
return errors
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 issubclass(engine_spec, BasicParametersMixin):
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(
self._properties.get("parameters", {}))
if errors:
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(
self._properties.get("parameters", None), # type: ignore
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: # pylint: disable=broad-except
url = make_url(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)
if not alive:
raise DatabaseOfflineError(
SupersetError(
message=__("Database is offline."),
error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR,
level=ErrorLevel.ERROR,
), )
def test_extract_errors(self):
"""
Test that custom error messages are extracted correctly.
"""
msg = 'psql: error: FATAL: role "testuser" does not exist'
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.TEST_CONNECTION_INVALID_USERNAME_ERROR,
message='The username "testuser" does not exist.',
level=ErrorLevel.ERROR,
extra={
"engine_name": "PostgreSQL",
"issue_codes": [
{
"code": 1012,
"message": (
"Issue 1012 - The username provided when "
"connecting to a database is not valid."
),
},
],
},
)
]
msg = 'psql: error: could not translate host name "locahost" to address: nodename nor servname provided, or not known'
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.TEST_CONNECTION_INVALID_HOSTNAME_ERROR,
message='The hostname "locahost" cannot be resolved.',
level=ErrorLevel.ERROR,
extra={
"engine_name": "PostgreSQL",
"issue_codes": [
{
"code": 1007,
"message": "Issue 1007 - The hostname provided can't be resolved.",
}
],
},
)
]
msg = dedent(
"""
psql: error: could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 12345?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 12345?
"""
)
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.TEST_CONNECTION_PORT_CLOSED_ERROR,
message='Port 12345 on hostname "localhost" refused the connection.',
level=ErrorLevel.ERROR,
extra={
"engine_name": "PostgreSQL",
"issue_codes": [
{"code": 1008, "message": "Issue 1008 - The port is closed."}
],
},
)
]
msg = dedent(
"""
psql: error: could not connect to server: Operation timed out
Is the server running on host "example.com" (93.184.216.34) and accepting
TCP/IP connections on port 12345?
"""
)
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.TEST_CONNECTION_HOST_DOWN_ERROR,
message=(
'The host "example.com" might be down, '
"and can't be reached on port 12345."
),
level=ErrorLevel.ERROR,
extra={
"engine_name": "PostgreSQL",
"issue_codes": [
{
"code": 1009,
"message": "Issue 1009 - The host might be down, and can't be reached on the provided port.",
}
],
},
)
]
# response with IP only
msg = dedent(
"""
psql: error: could not connect to server: Operation timed out
Is the server running on host "93.184.216.34" and accepting
TCP/IP connections on port 12345?
"""
)
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.TEST_CONNECTION_HOST_DOWN_ERROR,
message=(
'The host "93.184.216.34" might be down, '
"and can't be reached on port 12345."
),
level=ErrorLevel.ERROR,
extra={
"engine_name": "PostgreSQL",
"issue_codes": [
{
"code": 1009,
"message": "Issue 1009 - The host might be down, and can't be reached on the provided port.",
}
],
},
)
]
msg = 'FATAL: password authentication failed for user "postgres"'
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.TEST_CONNECTION_INVALID_PASSWORD_ERROR,
message=('The password provided for username "postgres" is incorrect.'),
level=ErrorLevel.ERROR,
extra={
"engine_name": "PostgreSQL",
"issue_codes": [
{
"code": 1013,
"message": (
"Issue 1013 - The password provided when "
"connecting to a database is not valid."
),
},
],
},
)
]
def test_extract_errors(self):
msg = "Generic Error"
result = PrestoEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message="Generic Error",
error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [{
"code":
1002,
"message":
"Issue 1002 - The database returned an unexpected error.",
}],
},
)
]
msg = "line 1:8: Column 'bogus' cannot be resolved"
result = PrestoEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message=
'We can\'t seem to resolve the column "bogus" at line 1:8.',
error_type=SupersetErrorType.COLUMN_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [
{
"code":
1003,
"message":
"Issue 1003 - There is a syntax error in the SQL query. Perhaps there was a misspelling or a typo.",
},
{
"code":
1004,
"message":
"Issue 1004 - The column was deleted or renamed in the database.",
},
],
},
)
]
msg = "line 1:15: Table 'tpch.tiny.region2' does not exist"
result = PrestoEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message=
"The table \"'tpch.tiny.region2'\" does not exist. A valid table must be used to run this query.",
error_type=SupersetErrorType.TABLE_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [
{
"code":
1003,
"message":
"Issue 1003 - There is a syntax error in the SQL query. Perhaps there was a misspelling or a typo.",
},
{
"code":
1005,
"message":
"Issue 1005 - The table was deleted or renamed in the database.",
},
],
},
)
]
msg = "line 1:15: Schema 'tin' does not exist"
result = PrestoEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message=
'The schema "tin" does not exist. A valid schema must be used to run this query.',
error_type=SupersetErrorType.SCHEMA_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [
{
"code":
1003,
"message":
"Issue 1003 - There is a syntax error in the SQL query. Perhaps there was a misspelling or a typo.",
},
{
"code":
1016,
"message":
"Issue 1005 - The schema was deleted or renamed in the database.",
},
],
},
)
]
msg = b"Access Denied: Invalid credentials"
result = PrestoEngineSpec.extract_errors(Exception(msg),
{"username": "******"})
assert result == [
SupersetError(
message=
'Either the username "alice" or the password is incorrect.',
error_type=SupersetErrorType.CONNECTION_ACCESS_DENIED_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [{
"code":
1014,
"message":
"Issue 1014 - Either the username or the password is wrong.",
}],
},
)
]
msg = "Failed to establish a new connection: [Errno 8] nodename nor servname provided, or not known"
result = PrestoEngineSpec.extract_errors(Exception(msg),
{"hostname": "badhost"})
assert result == [
SupersetError(
message='The hostname "badhost" cannot be resolved.',
error_type=SupersetErrorType.CONNECTION_INVALID_HOSTNAME_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [{
"code":
1007,
"message":
"Issue 1007 - The hostname provided can't be resolved.",
}],
},
)
]
msg = "Failed to establish a new connection: [Errno 60] Operation timed out"
result = PrestoEngineSpec.extract_errors(Exception(msg), {
"hostname": "badhost",
"port": 12345
})
assert result == [
SupersetError(
message=
'The host "badhost" might be down, and can\'t be reached on port 12345.',
error_type=SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [{
"code":
1009,
"message":
"Issue 1009 - The host might be down, and can't be reached on the provided port.",
}],
},
)
]
msg = "Failed to establish a new connection: [Errno 61] Connection refused"
result = PrestoEngineSpec.extract_errors(Exception(msg), {
"hostname": "badhost",
"port": 12345
})
assert result == [
SupersetError(
message=
'Port 12345 on hostname "badhost" refused the connection.',
error_type=SupersetErrorType.CONNECTION_PORT_CLOSED_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [{
"code":
1008,
"message":
"Issue 1008 - The port is closed."
}],
},
)
]
msg = "line 1:15: Catalog 'wrong' does not exist"
result = PrestoEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message='Unable to connect to catalog named "wrong".',
error_type=SupersetErrorType.CONNECTION_UNKNOWN_DATABASE_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Presto",
"issue_codes": [{
"code":
1015,
"message":
"Issue 1015 - Either the database is spelled incorrectly or does not exist.",
}],
},
)
]
def test_extract_errors(self):
"""
Test that custom error messages are extracted correctly.
"""
msg = 'psql: error: FATAL: role "testuser" does not exist'
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.
TEST_CONNECTION_INVALID_USERNAME_ERROR,
message='The username "testuser" does not exist.',
level=ErrorLevel.ERROR,
extra={"engine_name": "PostgreSQL"},
)
]
msg = 'psql: error: could not translate host name "locahost" to address: nodename nor servname provided, or not known'
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.
TEST_CONNECTION_INVALID_HOSTNAME_ERROR,
message='The hostname "locahost" cannot be resolved.',
level=ErrorLevel.ERROR,
extra={"engine_name": "PostgreSQL"},
)
]
msg = dedent("""
psql: error: could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 12345?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 12345?
""")
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.TEST_CONNECTION_PORT_CLOSED_ERROR,
message=
"Port 12345 on hostname localhost refused the connection.",
level=ErrorLevel.ERROR,
extra={"engine_name": "PostgreSQL"},
)
]
msg = dedent("""
psql: error: could not connect to server: Operation timed out
Is the server running on host "example.com" (93.184.216.34) and accepting
TCP/IP connections on port 12345?
""")
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.TEST_CONNECTION_HOST_DOWN_ERROR,
message=("The host example.com might be down, "
"and can't be reached on port 12345"),
level=ErrorLevel.ERROR,
extra={"engine_name": "PostgreSQL"},
)
]
# response with IP only
msg = dedent("""
psql: error: could not connect to server: Operation timed out
Is the server running on host "93.184.216.34" and accepting
TCP/IP connections on port 12345?
""")
result = PostgresEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.TEST_CONNECTION_HOST_DOWN_ERROR,
message=("The host 93.184.216.34 might be down, "
"and can't be reached on port 12345"),
level=ErrorLevel.ERROR,
extra={"engine_name": "PostgreSQL"},
)
]
def test_extract_errors(self):
"""
Test that custom error messages are extracted correctly.
"""
msg = 'FATAL: password authentication failed for user "wronguser"'
result = RedshiftEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_ACCESS_DENIED_ERROR,
message=
'Either the username "wronguser" or the password is incorrect.',
level=ErrorLevel.ERROR,
extra={
"invalid": ["username", "password"],
"engine_name":
"Amazon Redshift",
"issue_codes": [
{
"code":
1014,
"message":
"Issue 1014 - Either the username "
"or the password is wrong.",
},
{
"code":
1015,
"message":
"Issue 1015 - Either the database is "
"spelled incorrectly or does not exist.",
},
],
},
)
]
msg = ('redshift: error: could not translate host name "badhost" '
"to address: nodename nor servname provided, or not known")
result = RedshiftEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_INVALID_HOSTNAME_ERROR,
message='The hostname "badhost" cannot be resolved.',
level=ErrorLevel.ERROR,
extra={
"invalid": ["host"],
"engine_name":
"Amazon Redshift",
"issue_codes": [{
"code":
1007,
"message":
"Issue 1007 - The hostname provided "
"can't be resolved.",
}],
},
)
]
msg = dedent("""
psql: error: could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 12345?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 12345?
""")
result = RedshiftEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_PORT_CLOSED_ERROR,
message=
'Port 12345 on hostname "localhost" refused the connection.',
level=ErrorLevel.ERROR,
extra={
"invalid": ["host", "port"],
"engine_name":
"Amazon Redshift",
"issue_codes": [{
"code":
1008,
"message":
"Issue 1008 - The port is closed."
}],
},
)
]
msg = dedent("""
psql: error: could not connect to server: Operation timed out
Is the server running on host "example.com" (93.184.216.34) and accepting
TCP/IP connections on port 12345?
""")
result = RedshiftEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
message=('The host "example.com" might be down, '
"and can't be reached on port 12345."),
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Amazon Redshift",
"issue_codes": [{
"code":
1009,
"message":
"Issue 1009 - The host might be down, "
"and can't be reached on the provided port.",
}],
"invalid": ["host", "port"],
},
)
]
# response with IP only
msg = dedent("""
psql: error: could not connect to server: Operation timed out
Is the server running on host "93.184.216.34" and accepting
TCP/IP connections on port 12345?
""")
result = RedshiftEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
message=('The host "93.184.216.34" might be down, '
"and can't be reached on port 12345."),
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Amazon Redshift",
"issue_codes": [{
"code":
1009,
"message":
"Issue 1009 - The host might be down, "
"and can't be reached on the provided port.",
}],
"invalid": ["host", "port"],
},
)
]
msg = 'database "badDB" does not exist'
result = RedshiftEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_UNKNOWN_DATABASE_ERROR,
message=
'We were unable to connect to your database named "badDB".'
" Please verify your database name and try again.",
level=ErrorLevel.ERROR,
extra={
"engine_name":
"Amazon Redshift",
"issue_codes": [{
"code":
10015,
"message":
"Issue 1015 - Either the database is "
"spelled incorrectly or does not exist.",
}],
"invalid": ["database"],
},
)
]
def test_extract_errors(self):
msg = "403 POST https://bigquery.googleapis.com/bigquery/v2/projects/test-keel-310804/jobs?prettyPrint=false: Access Denied: Project User does not have bigquery.jobs.create permission in project profound-keel-310804"
result = BigQueryEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message="We were unable to connect to your database. Please confirm that your service account has the Viewer and Job User roles on the project.",
error_type=SupersetErrorType.CONNECTION_DATABASE_PERMISSIONS_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name": "Google BigQuery",
"issue_codes": [
{
"code": 1017,
"message": "",
}
],
},
)
]
msg = "bigquery error: 404 Not found: Dataset fakeDataset:bogusSchema was not found in location"
result = BigQueryEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message='The schema "bogusSchema" does not exist. A valid schema must be used to run this query.',
error_type=SupersetErrorType.SCHEMA_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name": "Google BigQuery",
"issue_codes": [
{
"code": 1003,
"message": "Issue 1003 - There is a syntax error in the SQL query. Perhaps there was a misspelling or a typo.",
},
{
"code": 1004,
"message": "Issue 1004 - The column was deleted or renamed in the database.",
},
],
},
)
]
msg = 'Table name "badtable" missing dataset while no default dataset is set in the request'
result = BigQueryEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message='The table "badtable" does not exist. A valid table must be used to run this query.',
error_type=SupersetErrorType.TABLE_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name": "Google BigQuery",
"issue_codes": [
{
"code": 1003,
"message": "Issue 1003 - There is a syntax error in the SQL query. Perhaps there was a misspelling or a typo.",
},
{
"code": 1005,
"message": "Issue 1005 - The table was deleted or renamed in the database.",
},
],
},
)
]
msg = "Unrecognized name: badColumn at [1:8]"
result = BigQueryEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message='We can\'t seem to resolve column "badColumn" at line 1:8.',
error_type=SupersetErrorType.COLUMN_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name": "Google BigQuery",
"issue_codes": [
{
"code": 1003,
"message": "Issue 1003 - There is a syntax error in the SQL query. Perhaps there was a misspelling or a typo.",
},
{
"code": 1004,
"message": "Issue 1004 - The column was deleted or renamed in the database.",
},
],
},
)
]
msg = 'Syntax error: Expected end of input but got identifier "fromm"'
result = BigQueryEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
message='Please check your query for syntax errors at or near "fromm". Then, try running your query again.',
error_type=SupersetErrorType.SYNTAX_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name": "Google BigQuery",
"issue_codes": [
{
"code": 1030,
"message": "Issue 1030 - The query has a syntax error.",
}
],
},
)
]
def test_extract_errors(self):
"""
Test that custom error messages are extracted correctly.
"""
msg = dedent(
"""
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (locahost)
"""
)
result = MssqlEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_INVALID_HOSTNAME_ERROR,
message='The hostname "locahost" cannot be resolved.',
level=ErrorLevel.ERROR,
extra={
"engine_name": "Microsoft SQL Server",
"issue_codes": [
{
"code": 1007,
"message": "Issue 1007 - The hostname provided can't be resolved.",
}
],
},
)
]
msg = dedent(
"""
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (localhost)
Net-Lib error during Connection refused (61)
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (localhost)
Net-Lib error during Connection refused (61)
"""
)
result = MssqlEngineSpec.extract_errors(
Exception(msg), context={"port": 12345, "hostname": "localhost"}
)
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_PORT_CLOSED_ERROR,
message='Port 12345 on hostname "localhost" refused the connection.',
level=ErrorLevel.ERROR,
extra={
"engine_name": "Microsoft SQL Server",
"issue_codes": [
{"code": 1008, "message": "Issue 1008 - The port is closed."}
],
},
)
]
msg = dedent(
"""
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (example.com)
Net-Lib error during Operation timed out (60)
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (example.com)
Net-Lib error during Operation timed out (60)
"""
)
result = MssqlEngineSpec.extract_errors(
Exception(msg), context={"port": 12345, "hostname": "example.com"}
)
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
message=(
'The host "example.com" might be down, '
"and can't be reached on port 12345."
),
level=ErrorLevel.ERROR,
extra={
"engine_name": "Microsoft SQL Server",
"issue_codes": [
{
"code": 1009,
"message": "Issue 1009 - The host might be down, and can't be reached on the provided port.",
}
],
},
)
]
msg = dedent(
"""
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (93.184.216.34)
Net-Lib error during Operation timed out (60)
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist (93.184.216.34)
Net-Lib error during Operation timed out (60)
"""
)
result = MssqlEngineSpec.extract_errors(
Exception(msg), context={"port": 12345, "hostname": "93.184.216.34"}
)
assert result == [
SupersetError(
error_type=SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
message=(
'The host "93.184.216.34" might be down, '
"and can't be reached on port 12345."
),
level=ErrorLevel.ERROR,
extra={
"engine_name": "Microsoft SQL Server",
"issue_codes": [
{
"code": 1009,
"message": "Issue 1009 - The host might be down, and can't be reached on the provided port.",
}
],
},
)
]
msg = dedent(
"""
DB-Lib error message 20018, severity 14:
General SQL Server error: Check messages from the SQL Server
DB-Lib error message 20002, severity 9:
Adaptive Server connection failed (mssqldb.cxiotftzsypc.us-west-2.rds.amazonaws.com)
DB-Lib error message 20002, severity 9:
Adaptive Server connection failed (mssqldb.cxiotftzsypc.us-west-2.rds.amazonaws.com)
"""
)
result = MssqlEngineSpec.extract_errors(
Exception(msg), context={"username": "******", "database": "testdb"}
)
assert result == [
SupersetError(
message='Either the username "testuser", password, or database name "testdb" is incorrect.',
error_type=SupersetErrorType.CONNECTION_ACCESS_DENIED_ERROR,
level=ErrorLevel.ERROR,
extra={
"engine_name": "Microsoft SQL Server",
"issue_codes": [
{
"code": 1014,
"message": "Issue 1014 - Either the username or "
"the password is wrong.",
},
{
"code": 1015,
"message": "Issue 1015 - Either the database is "
"spelled incorrectly or does not exist.",
},
],
},
)
]
def test_extract_errors(self):
"""
Test that custom error messages are extracted correctly.
"""
msg = "mysql: Access denied for user 'test'@'testuser.com'. "
result = MySQLEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.
TEST_CONNECTION_ACCESS_DENIED_ERROR,
message=
'Either the username "test" or the password is incorrect.',
level=ErrorLevel.ERROR,
extra={
"engine_name":
"MySQL",
"issue_codes": [{
"code":
1014,
"message":
"Issue 1014 - Either the username or the password is wrong.",
}],
},
)
]
msg = "mysql: Unknown MySQL server host 'badhostname.com'. "
result = MySQLEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.
TEST_CONNECTION_INVALID_HOSTNAME_ERROR,
message='Unknown MySQL server host "badhostname.com".',
level=ErrorLevel.ERROR,
extra={
"engine_name":
"MySQL",
"issue_codes": [{
"code":
1007,
"message":
"Issue 1007 - The hostname provided can't be resolved.",
}],
},
)
]
msg = "mysql: Can't connect to MySQL server on 'badconnection.com'."
result = MySQLEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.TEST_CONNECTION_HOST_DOWN_ERROR,
message=
'The host "badconnection.com" might be down and can\'t be reached.',
level=ErrorLevel.ERROR,
extra={
"engine_name":
"MySQL",
"issue_codes": [{
"code":
1007,
"message":
"Issue 1007 - The hostname provided can't be resolved.",
}],
},
)
]
msg = "mysql: Can't connect to MySQL server on '93.184.216.34'."
result = MySQLEngineSpec.extract_errors(Exception(msg))
assert result == [
SupersetError(
error_type=SupersetErrorType.TEST_CONNECTION_HOST_DOWN_ERROR,
message=
'The host "93.184.216.34" might be down and can\'t be reached.',
level=ErrorLevel.ERROR,
extra={
"engine_name":
"MySQL",
"issue_codes": [{
"code":
10007,
"message":
"Issue 1007 - The hostname provided can't be resolved.",
}],
},
)
]
def validate_parameters(
cls,
parameters: GSheetsParametersType,
) -> List[SupersetError]:
errors: List[SupersetError] = []
credentials_info = parameters.get("credentials_info")
table_catalog = parameters.get("catalog", {})
if not table_catalog:
errors.append(
SupersetError(
message="URL is required",
error_type=SupersetErrorType.
CONNECTION_MISSING_PARAMETERS_ERROR,
level=ErrorLevel.WARNING,
extra={
"invalid": ["catalog"],
"name": "",
"url": ""
},
), )
return errors
# We need a subject in case domain wide delegation is set, otherwise the
# check will fail. This means that the admin will be able to add sheets
# that only they have access, even if later users are not able to access
# them.
subject = g.user.email if g.user else None
engine = create_engine(
"gsheets://",
service_account_info=credentials_info,
subject=subject,
)
conn = engine.connect()
for name, url in table_catalog.items():
if not name:
errors.append(
SupersetError(
message="Sheet name is required",
error_type=SupersetErrorType.
CONNECTION_MISSING_PARAMETERS_ERROR,
level=ErrorLevel.WARNING,
extra={
"invalid": [],
"name": name,
"url": url
},
), )
try:
results = conn.execute(f'SELECT * FROM "{url}" LIMIT 1')
results.fetchall()
except Exception: # pylint: disable=broad-except
errors.append(
SupersetError(
message="URL could not be identified",
error_type=SupersetErrorType.
TABLE_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.WARNING,
extra={
"invalid": ["catalog"],
"name": name,
"url": url
},
), )
return errors
def test_validate_parameters_catalog(mocker, app_context):
from superset.db_engine_specs.gsheets import GSheetsEngineSpec
g = mocker.patch("superset.db_engine_specs.gsheets.g")
g.user.email = "*****@*****.**"
create_engine = mocker.patch(
"superset.db_engine_specs.gsheets.create_engine")
conn = create_engine.return_value.connect.return_value
results = conn.execute.return_value
results.fetchall.side_effect = [
ProgrammingError("The caller does not have permission"),
[(1, )],
ProgrammingError("Unsupported table: https://www.google.com/"),
]
parameters = {
"table_catalog": {
"private_sheet": "https://docs.google.com/spreadsheets/d/1/edit",
"public_sheet":
"https://docs.google.com/spreadsheets/d/1/edit#gid=1",
"not_a_sheet": "https://www.google.com/",
},
}
errors = GSheetsEngineSpec.validate_parameters(parameters)
assert errors == [
SupersetError(
message=("Unable to connect to spreadsheet private_sheet at "
"https://docs.google.com/spreadsheets/d/1/edit"),
error_type=SupersetErrorType.TABLE_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.WARNING,
extra={
"name":
"private_sheet",
"url":
"https://docs.google.com/spreadsheets/d/1/edit",
"issue_codes": [
{
"code":
1003,
"message":
("Issue 1003 - There is a syntax error in the SQL query. "
"Perhaps there was a misspelling or a typo."),
},
{
"code":
1005,
"message":
("Issue 1005 - The table was deleted or renamed in the "
"database."),
},
],
},
),
SupersetError(
message=("Unable to connect to spreadsheet not_a_sheet at "
"https://www.google.com/"),
error_type=SupersetErrorType.TABLE_DOES_NOT_EXIST_ERROR,
level=ErrorLevel.WARNING,
extra={
"name":
"not_a_sheet",
"url":
"https://www.google.com/",
"issue_codes": [
{
"code":
1003,
"message":
("Issue 1003 - There is a syntax error in the SQL query. "
"Perhaps there was a misspelling or a typo."),
},
{
"code":
1005,
"message":
("Issue 1005 - The table was deleted or renamed in the "
"database.", ),
},
],
},
),
]
create_engine.assert_called_with(
"gsheets://",
service_account_info=None,
subject="*****@*****.**",
)
