You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by be...@apache.org on 2023/07/28 01:10:12 UTC
[superset] 01/02: More docs
This is an automated email from the ASF dual-hosted git repository.
beto pushed a commit to branch db-diagnostics
in repository https://gitbox.apache.org/repos/asf/superset.git
commit b321ed1d65bcef0228f8ae0c7ab1ff779c2d2cc4
Author: Beto Dealmeida <ro...@dealmeida.net>
AuthorDate: Thu Jul 27 18:09:11 2023 -0700
More docs
---
superset/cli/test_db.py | 92 +++------
superset/db_engine_specs/README.md | 354 ++++++++++++++++++++++++++++-------
superset/db_engine_specs/lib.py | 102 +++++++---
superset/db_engine_specs/postgres.py | 2 +
4 files changed, 395 insertions(+), 155 deletions(-)
diff --git a/superset/cli/test_db.py b/superset/cli/test_db.py
index d1442fc198..e9f8414ccd 100644
--- a/superset/cli/test_db.py
+++ b/superset/cli/test_db.py
@@ -20,7 +20,7 @@ from __future__ import annotations
import sys
from collections import defaultdict
from datetime import datetime
-from typing import Any, Callable, Dict, Type
+from typing import Any, Callable
import click
import yaml
@@ -38,60 +38,19 @@ from sqlalchemy import (
Table,
)
from sqlalchemy.engine import Engine
-from sqlalchemy.engine.url import make_url
from sqlalchemy.exc import NoSuchModuleError
+from superset.database.utils import make_url_safe
from superset.db_engine_specs import load_engine_specs
from superset.db_engine_specs.base import BaseEngineSpec
-from superset.db_engine_specs.lib import diagnose
-
-LIMIT_METHODS = {
- "FORCE_LIMIT": "modifies the query, replacing an existing LIMIT or adding a new one",
- "WRAP_SQL": "wraps the original query in a SELECT * with a LIMIT",
- "FETCH_MANY": "runs the query unmodified but fetchs only LIMIT rows from the cursor",
-}
-
-DATABASE_DETAILS = {
- "Supports JOINs": "joins",
- "Supports subqueries": "subqueries",
- "Allows aliases in the SELECT statement": "alias_in_select",
- "Allows referencing aliases in the ORDER BY statement": "alias_in_orderby",
- "Supports secondary time columns": "secondary_time_columns",
- "Allows ommiting time filters from inline GROUP BYs": "time_groupby_inline",
- "Able to use source column when an alias overshadows it": "alias_to_source_column",
- "Allows aggregations in ORDER BY not present in the SELECT": "order_by_not_in_select",
- "Allows expressions in ORDER BY": "expressions_in_orderby",
- "Allows CTE as a subquery": "cte_in_subquery",
- "Allows LIMIT clause (instead of TOP)": "limit_clause",
- "Maximum column name": "max_column_name",
- "Allows comments": "sql_comments",
- "Colons must be escaped": "escaped_colons",
-}
-
-BASIC_FEATURES = {
- "Masks/unmasks encrypted_extra": "masked_encrypted_extra",
- "Has column type mappings": "column_type_mapping",
- "Returns a list of function names": "function_names",
-}
-NICE_TO_HAVE_FEATURES = {
- "Supports user impersonation": "user_impersonation",
- "Support file upload": "file_upload",
- "Returns extra table metadata": "extra_table_metadata",
- "Maps driver exceptions to Superset exceptions": "dbapi_exception_mapping",
- "Parses error messages and returns Superset errors": "custom_errors",
- "Supports changing the schema per-query": "dynamic_schema",
- "Supports catalogs": "catalog",
- "Supports changing the catalog per-query": "dynamic_catalog",
- "Can be connected thru an SSH tunnel": "ssh_tunneling",
- "Allows query to be canceled": "query_cancelation",
- "Returns additional metrics on dataset creation": "get_metrics",
- "Supports querying the latest partition only": "where_latest_partition",
-}
-ADVANCED_FEATURES = {
- "Expands complex types (arrays, structs) into rows/columns": "expand_data",
- "Supports query cost estimation": "query_cost_estimation",
- "Supports validating SQL before running query": "sql_validation",
-}
+from superset.db_engine_specs.lib import (
+ ADVANCED_FEATURES,
+ BASIC_FEATURES,
+ DATABASE_DETAILS,
+ diagnose,
+ LIMIT_METHODS,
+ NICE_TO_HAVE_FEATURES,
+)
metadata_obj = MetaData()
@@ -119,7 +78,7 @@ TestType = Callable[[Console, Engine], None]
class TestRegistry:
def __init__(self) -> None:
- self.tests: Dict[str, Any] = defaultdict(list)
+ self.tests: dict[str, Any] = defaultdict(list)
def add(self, *dialects: str) -> Callable[[TestType], TestType]:
def decorator(func: TestType) -> TestType:
@@ -220,7 +179,7 @@ def collect_connection_info(
console: Console,
sqlalchemy_uri: str,
raw_connect_args: str | None = None,
-) -> Dict[str, Any]:
+) -> dict[str, Any]:
"""
Collect ``connect_args`` if needed.
"""
@@ -244,14 +203,14 @@ def collect_connection_info(
def test_db_engine_spec(
console: Console,
sqlalchemy_uri: str,
-) -> Type[BaseEngineSpec] | None:
+) -> type[BaseEngineSpec] | None:
"""
Test the DB engine spec, if available.
"""
- spec: Type[BaseEngineSpec] | None = None
+ spec: type[BaseEngineSpec] | None = None
for spec in load_engine_specs():
try:
- supported = spec.supports_url(make_url(sqlalchemy_uri))
+ supported = spec.supports_url(make_url_safe(sqlalchemy_uri))
except NoSuchModuleError:
console.print("[red]No SQLAlchemy dialect found for the URI!")
console.print("[bold]Exiting...")
@@ -284,7 +243,7 @@ def test_db_engine_spec(
console.print(" - Method used to apply LIMIT to queries:", info["limit_method"])
for k, v in LIMIT_METHODS.items():
console.print(f" - {k}: {v}")
- for feature, key in DATABASE_DETAILS.items():
+ for key, feature in DATABASE_DETAILS.items():
console.print(f" - {feature}:", info[key])
console.print("[bold]Checking for basic features...")
@@ -293,26 +252,28 @@ def test_db_engine_spec(
score = " (+1)" if v else ""
console.print(f" - {k}: {v}{score}")
for k, v in BASIC_FEATURES.items():
- score = " (+10)" if info[v] else ""
- console.print(f"{k}: {info[v]}{score}")
+ score = " (+10)" if info[k] else ""
+ console.print(f"{v}: {info[k]}{score}")
console.print("[bold]Checking for nice-to-have features...")
for k, v in NICE_TO_HAVE_FEATURES.items():
- score = " (+10)" if info[v] else ""
- console.print(f"{k}: {info[v]}{score}")
+ score = " (+10)" if info[k] else ""
+ console.print(f"{v}: {info[k]}{score}")
console.print("[bold]Checking for advanced features...")
for k, v in ADVANCED_FEATURES.items():
- score = " (+10)" if info[v] else ""
- console.print(f"{k}: {info[v]}{score}")
+ score = " (+10)" if info[k] else ""
+ console.print(f"{v}: {info[k]}{score}")
console.print("[bold]Overall score: {score}/{max_score}".format(**info))
+ return spec
+
def test_sqlalchemy_dialect(
console: Console,
sqlalchemy_uri: str,
- connect_args: Dict[str, Any],
+ connect_args: dict[str, Any],
) -> Engine:
"""
Test the SQLAlchemy dialect, making sure it supports everything Superset needs.
@@ -448,8 +409,7 @@ def test_database_connectivity(console: Console, engine: Engine) -> None:
sys.exit(1)
# run engine-specific tests
- tests = registry.get_tests(engine.dialect.name)
- if tests:
+ if tests := registry.get_tests(engine.dialect.name):
console.print("[bold]Running engine-specific tests...")
for test in tests:
test(console, engine)
diff --git a/superset/db_engine_specs/README.md b/superset/db_engine_specs/README.md
index e05ac9cab1..16503ee07c 100644
--- a/superset/db_engine_specs/README.md
+++ b/superset/db_engine_specs/README.md
@@ -1,5 +1,4 @@
-Database engine specifications
-==============================
+# Database engine specifications
Superset uses [SQLAlchemy](https://www.sqlalchemy.org/) as an abstraction layer for running queries and fetching metadata from tables (like column names and types). Unfortunately, while SQLAlchemy offers enough functionality to allow connecting Superset to dozens of databases, there are still implementation details that differ across them. Because of this, Superset has an additional abstraction on top of SQLAlchemy, called a "database engine specification" or, simply, "DB engine spec".
@@ -31,72 +30,293 @@ Over time, more and more functionality was added to DB engine specs, including v
Note that DB engine specs are completely optional. Superset can connect to any database supported by SQLAlchemy (or 3rd party dialects) even if there's no DB engine spec associated with it. But DB engine specs greatly improve the experience of working with a database in Superset.
-Features
---------
-
-The table below summarizes the information about the status of all DB engine specs Superset is aware of (note that this includes 3rd party DB engine specs):
-
-| Feature | Details | Base | Amazon Athena | Amazon DynamoDB | Amazon Redshift | Apache Drill | Apache Druid | Apache Hive | Apache Impala | Apache Kylin | Apache Pinot | Apache Solr | Apac [...]
-|----------------------------|----------------------------------------------------------------------------------------|-------------------------------|---------------------------------|-----------------------------------|-----------------------------------|--------------------------------|--------------------------------|-------------------------------|---------------------------------|--------------------------------|--------------------------------|-------------------------------|----- [...]
-| Module | | superset.db_engine_specs.base | superset.db_engine_specs.athena | superset.db_engine_specs.dynamodb | superset.db_engine_specs.redshift | superset.db_engine_specs.drill | superset.db_engine_specs.druid | superset.db_engine_specs.hive | superset.db_engine_specs.impala | superset.db_engine_specs.kylin | superset.db_engine_specs.pinot | superset.db_engine_specs.solr | supe [...]
-| Limit method | In general, FORCE_LIMIT > WRAP_SQL > FETCH_MANY | FETCH_MANY | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORC [...]
-| Joins | | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | TRUE | TRUE | TRUE | FALSE | FALSE | [...]
-| Subqueries | | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | FALSE | [...]
-| Alias in SELECT | | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | TRUE | [...]
-| Alias in ORDER BY | | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | TRUE | [...]
-| Secondary time columns | | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| Time GROUP BY inline | | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| Alias to source column | | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| ORDER BY must be in SELECT | | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | TRUE | TRUE | TRUE | TRUE | [...]
-| Expression in ORDER BY | | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| CTE in subquery | | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | [...]
-| LIMIT clause | | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | [...]
-| Maximum column name | | | | | 127 | | | 767 | | | | | [...]
-| Comments | | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | [...]
-| Escaped colons | | TRUE | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | [...]
-| Time grains | SECOND | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | TRUE | TRUE | FALSE | [...]
-| | FIVE_SECONDS | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| | THIRTY_SECONDS | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| | MINUTE | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | [...]
-| | FIVE_MINUTES | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE | TRUE | FALSE | [...]
-| | TEN_MINUTES | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE | TRUE | FALSE | [...]
-| | FIFTEEN_MINUTES | FALSE | FALSE | FALSE | FALSE | TRUE | TRUE | FALSE | FALSE | FALSE | TRUE | FALSE | [...]
-| | THIRTY_MINUTES | FALSE | FALSE | FALSE | FALSE | TRUE | TRUE | FALSE | FALSE | FALSE | TRUE | FALSE | [...]
-| | HALF_HOUR | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| | HOUR | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | [...]
-| | SIX_HOURS | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| | DAY | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | [...]
-| | WEEK | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | [...]
-| | WEEK_STARTING_SUNDAY | FALSE | TRUE | TRUE | FALSE | FALSE | TRUE | TRUE | FALSE | FALSE | FALSE | FALSE | [...]
-| | WEEK_STARTING_MONDAY | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| | WEEK_ENDING_SATURDAY | FALSE | TRUE | TRUE | FALSE | FALSE | TRUE | TRUE | FALSE | FALSE | FALSE | FALSE | [...]
-| | WEEK_ENDING_SUNDAY | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| | MONTH | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | [...]
-| | QUARTER | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | [...]
-| | QUARTER_YEAR | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| | YEAR | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | [...]
-| Mask encrypted_extra | | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| Column type mapping | | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE | FALSE | [...]
-| Function names | | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE | FALSE | [...]
-| User impersonation | | TRUE | FALSE | FALSE | FALSE | TRUE | FALSE | TRUE | FALSE | FALSE | FALSE | FALSE | [...]
-| File upload | | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | [...]
-| Extra table metadata | | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| DB API exception mapping | | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| Custom errors | | FALSE | TRUE | FALSE | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| Dynamic schema | | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE | TRUE | FALSE | FALSE | FALSE | FALSE | [...]
-| Catalog | | FALSE | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| Dynamic catalog | | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| SSH tunneling | | FALSE | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | [...]
-| Query cancelation | | FALSE | FALSE | FALSE | TRUE | FALSE | FALSE | TRUE | TRUE | FALSE | FALSE | FALSE | [...]
-| Get metrics | Returns metrics when a dataset is first added. Currently used only in 3rd party specs. | COUNT(*) | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| WHERE latest partition | | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE | FALSE | [...]
-| Expand complex data types | This could have a single implementation for all specs, instead of being per-spec. | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| Query cost estimation | | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-| SQL validation | Implemented outside the spec. | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | [...]
-
-Database information
---------------------
+## Features
+
+The table below (generated via `python superset/db_engine_specs/lib.py`) summarizes the information about the status of all DB engine specs in Superset (note that this excludes 3rd party DB engine specs):
+
+| Feature | Amazon Athena | Amazon DynamoDB | Amazon Redshift | Apache Drill | Apache Druid | Apache Hive | Apache Impala | Apache Kylin | Apache Pinot | Apache Solr | Apache Spark SQL | Ascend | Aurora MySQL (Data API) | Aurora PostgreSQL (Data API) | Azure Synapse | ClickHouse | ClickHouse Connect (Superset) | CockroachDB | CrateDB | Databricks | Databricks Interactive Cluster | Databricks SQL Endpoint | Dremio | DuckDB | ElasticSearch (OpenDistro SQL) | ElasticSearch (SQL API) | Exaso [...]
+| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---| ---|
+| Module | superset.db_engine_specs.athena | superset.db_engine_specs.dynamodb | superset.db_engine_specs.redshift | superset.db_engine_specs.drill | superset.db_engine_specs.druid | superset.db_engine_specs.hive | superset.db_engine_specs.impala | superset.db_engine_specs.kylin | superset.db_engine_specs.pinot | superset.db_engine_specs.solr | superset.db_engine_specs.spark | superset.db_engine_specs.ascend | superset.db_engine_specs.aurora | superset.db_engine_specs.aurora | superset.d [...]
+| Method used to limit the rows in the subquery | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | WRAP_SQL | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | FETCH_MANY | FORCE_LIMIT | FORCE_LIMIT | FORCE_LIMIT | WRAP_SQL | F [...]
+| Supports JOINs | True | True | True | True | False | True | True | True | False | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | False | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True |
+| Supports subqueries | True | True | True | True | True | True | True | True | False | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True |
+| Allows aliases in the SELECT statement | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True |
+| Allows referencing aliases in the ORDER BY statement | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True |
+| Supports secondary time columns | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | True | False | False | False | False | False | False | False | True | True | False | False | False | False | False | False | False | True | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False |
+| Allows ommiting time filters from inline GROUP BYs | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | True | False | False | False | False | False | False | False | True | True | False | False | False | False | False | False | False | True | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False |
+| Able to use source column when an alias overshadows it | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | True | False | False |
+| Allows aggregations in ORDER BY not present in the SELECT | True | True | True | True | True | False | True | True | True | True | False | True | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True |
+| Allows expressions in ORDER BY | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False |
+| Allows CTE as a subquery | True | True | True | True | True | True | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | False | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True |
+| Allows LIMIT clause (instead of TOP) | True | True | True | True | True | True | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | False | True | True | True |
+| Maximum column name | None | None | 127 | None | None | 767 | None | None | None | None | 767 | None | 64 | 63 | 128 | None | None | 63 | None | None | 767 | None | None | None | None | None | 128 | None | None | 128 | None | 30 | None | None | None | 128 | 64 | 30 | 30 | None | None | 63 | None | 30 | None | None | 256 | 64 | 30 | None | None | None |
+| Allows comments | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | False | False | True | True | True | True | True | True | True | False | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True |
+| Colons must be escaped | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True |
+| Has time grain SECOND | True | True | True | True | True | True | False | True | True | False | True | True | True | True | True | False | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | False | True | True | True |
+| Has time grain FIVE_SECONDS | False | False | False | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | True | True | False | False | False | False | False | False |
+| Has time grain THIRTY_SECONDS | False | False | False | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | True | True | False | False | False | False | False | False |
+| Has time grain MINUTE | True | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True |
+| Has time grain FIVE_MINUTES | False | False | False | False | True | False | False | False | True | False | False | False | False | False | True | True | True | False | False | False | False | False | False | False | False | False | False | False | False | True | True | False | False | False | True | True | False | False | False | False | False | False | False | False | True | True | True | False | False | False | False | False |
+| Has time grain TEN_MINUTES | False | False | False | False | True | False | False | False | True | False | False | False | False | False | True | True | True | False | False | False | False | False | False | False | False | False | False | False | False | True | True | False | False | False | True | True | False | False | False | False | False | False | False | False | True | True | True | False | False | False | False | False |
+| Has time grain FIFTEEN_MINUTES | False | False | False | True | True | False | False | False | True | False | False | False | False | False | True | True | True | False | False | False | False | False | False | False | False | False | False | False | False | True | True | False | False | False | True | True | False | False | False | False | False | False | False | False | True | True | True | False | False | False | False | False |
+| Has time grain THIRTY_MINUTES | False | False | False | True | True | False | False | False | True | False | False | False | False | False | True | True | True | False | False | False | False | False | False | False | False | False | False | False | False | True | True | False | False | False | False | True | False | False | False | False | False | False | False | False | True | True | True | False | False | False | False | False |
+| Has time grain HALF_HOUR | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | False | False | False | True | False | False | False | False | False | False | False | False | False | True | True | False | False | False | False | False | False |
+| Has time grain HOUR | True | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True |
+| Has time grain SIX_HOURS | False | False | False | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | True | True | False | False | False | False | False | False |
+| Has time grain DAY | True | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True |
+| Has time grain WEEK | True | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | False | False | True | False | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True |
+| Has time grain WEEK_STARTING_SUNDAY | True | True | False | False | True | True | False | False | False | False | True | False | False | False | True | False | False | False | False | True | True | True | False | False | False | False | False | False | False | False | True | False | False | False | True | True | False | False | False | False | True | False | False | False | True | True | False | False | False | True | False | True |
+| Has time grain WEEK_STARTING_MONDAY | False | True | False | False | False | False | False | False | False | False | False | False | True | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | True | False | False | False | True | True | True | False | False | False | True | False | False | False | True | True | False | True | False | True | False | True |
+| Has time grain WEEK_ENDING_SATURDAY | True | True | False | False | True | True | False | False | False | False | True | False | False | False | False | False | False | False | False | True | True | True | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | True | False | False | False | True | True | False | False | False | True | False | True |
+| Has time grain WEEK_ENDING_SUNDAY | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | True | False | False | False | True | True | False | False | False | True | False | True |
+| Has time grain MONTH | True | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True |
+| Has time grain QUARTER | True | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | False | False | True | False | True | True | True | True | True | False | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True |
+| Has time grain QUARTER_YEAR | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | True | False | False | False | False | False | False | True | True | False | False | False | False | False | False |
+| Has time grain YEAR | True | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True |
+| Masks/unmasks encrypted_extra | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False |
+| Has column type mappings | False | False | False | False | False | True | False | False | False | False | True | False | True | True | True | True | True | True | False | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | True | True | True | False | False | False | True | True | False | False | False | False | False | True | False | True | False | True |
+| Returns a list of function names | False | False | False | False | False | True | False | False | False | False | True | False | False | False | False | True | True | False | False | False | True | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | True | False | False | False | True | True | False | False | False | True | False | True |
+| Supports user impersonation | False | False | False | True | False | True | False | False | False | False | True | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | True | False | False |
+| Support file upload | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | False | False | True | True | True | True | True | True | True | True | True | True | True | True | True | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True |
+| Returns extra table metadata | False | False | False | False | False | True | False | False | False | False | True | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | True | True | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | True | False | False |
+| Maps driver exceptions to Superset exceptions | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False |
+| Parses error messages and returns Superset errors | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False |
+| Supports changing the schema per-query | False | False | False | True | False | True | False | False | False | False | True | False | True | True | False | False | False | True | False | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | False | False | False | True | True | False | False | False | False | True | True | False | True | False | True |
+| Supports catalogs | False | False | True | False | False | False | False | False | False | False | False | False | False | True | False | False | False | True | False | False | False | False | False | False | False | False | False | False | False | True | False | False | True | False | False | False | False | False | False | True | False | True | False | True | False | False | True | False | False | False | True | False |
+| Supports changing the catalog per-query | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False |
+| Can be connected thru an SSH tunnel | False | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | True | False | False | True | True | True | True | True | True | True | True | True | True | True | True | True | False | False | True | True | True | True | True | True |
+| Allows query to be canceled | False | False | True | False | False | True | True | False | False | False | True | True | True | True | False | False | False | True | False | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | True | False | False | True | True | False | False | False | False | True | True | False | True | False | False |
+| Returns additional metrics on dataset creation | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False |
+| Supports querying the latest partition only | False | False | False | False | False | True | False | False | False | False | True | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | True | False | True |
+| Expands complex types (arrays, structs) into rows/columns | False | False | False | False | False | True | False | False | False | False | True | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | False | False |
+| Supports query cost estimation | False | False | False | False | False | True | False | False | False | False | True | False | False | True | False | False | False | True | False | False | True | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | False | True | True | False | False | False | False | False | False | False | True | False | True |
+| Supports validating SQL before running query | False | False | False | False | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | True | False | False | False | False | False | False | False | False | False | False | False |
+| Score | 20 | 32 | 48 | 50 | 37 | 120 | 37 | 28 | 32 | 20 | 120 | 38 | 59 | 88 | 44 | 41 | 41 | 78 | 28 | 40 | 120 | 30 | 28 | 28 | 26 | 26 | 28 | 26 | 28 | 63 | 61 | 28 | 38 | 26 | 44 | 44 | 59 | 38 | 28 | 38 | 132 | 78 | 28 | 37 | 41 | 41 | 62 | 59 | 27 | 112 | 38 | 82 |
+
+## Database information
A DB engine spec has attributes that describe the underlying database engine, so that Superset can know how to build and run queries. For example, some databases don't support subqueries, which are needed for some of the queries produced by Superset for certain charts. When a database doesn't support subqueries the query is run in two-steps, using the results from the first query to build the second query.
These attributes and their default values (set in the base class, `BaseEngineSpec`) are described below:
+
+### `limit_method = LimitMethod.FORCE_LIMIT`
+
+When running user queries in SQL Lab, Superset needs to limit the number of rows returned. The reason for that is cost and performance: there's no point in running a query that produces millions of rows when they can't be loaded into the browser.
+
+For most databases this is done by parsing the user submitted query and applying a limit, if one is not present, or replacing the existing limit if it's larger. This is called the `FORCE_LIMIT` method, and is the most efficient, since the database will produce at most the number of rows that Superset will display.
+
+For some databases this method might not work, and they can use the `WRAP_SQL` method, which wraps the original query in a `SELECT *` and applies a limit via the SQLAlchemy dialect, which should get translated to the correct syntax. This method might be inneficient, since the database optimizer might not be able to push the limit to the inner query.
+
+Finally, as a last resource there is the `FETCH_MANY` method. When a DB engine spec uses this method the query runs unmodified, but Superset fetches only a certain number of rows from the cursor. It's possible that a database using this method can optimize the query execution and compute rows as they are being read by the cursor, but it's unlikely. This makes this method the least efficient of the three.
+
+Note that when Superset runs a query with a given limit, say 100, it always modifies the query to request one additional row (`LIMIT 101`, in this case). This extra row is dropped before the results are returned to the user, but it allows Superset to inform the users that the query was indeed limited. Otherwise a query with `LIMIT 100` that returns exactly 100 rows would seem like it was limited, when in fact it was not.
+
+### `allows_joins = True`
+
+Not all databases support `JOIN`s. When building complex charts, Superset will try to join the table to itself in order to compute `top_n` groups, for example. If the database doesn't support joins Superset will instead run a prequery, and use the results to build the final query.
+
+### `allows_subqueries = True`
+
+Similarly, not all databases support subqueries. For more complex charts Superset will build subqueries if possible, or run the query in two-steps otherwise.
+
+### `allows_alias_in_select = True`
+
+Does the DB support aliases in the projection of a query, eg:
+
+```sql
+SELECT COUNT(*) AS cnt
+```
+
+Superset will try to use aliases whenever possible, in order to give friendly names to expressions.
+
+### `allows_alias_in_orderby = True`
+
+Does the DB support referencing alias in the `GROUP BY`, eg:
+
+```sql
+SELECT
+ UPPER(country_of_origin) AS country
+ COUNT(*) AS cnt
+FROM
+ some_table
+GROUP BY
+ country
+```
+
+Otherwise the query is written as:
+
+```sql
+SELECT
+ UPPER(country_of_origin) AS country
+ COUNT(*) AS cnt
+FROM
+ some_table
+GROUP BY
+ UPPER(country_of_origin)
+```
+
+### `time_secondary_columns = False`
+
+Datasets can have a main datatime column (`main_dttm_col`), but can also have secondary time columns. When this attribute is true, wheneve the secondary columns are filtered, the same filter is applied to the main datetime column.
+
+This might be useful if you have a table partitioned on a daily `ds` column in Hive (which doesn't support indexes), and a secondary column with the timestamp of the events, ie:
+
+| ds | event | ... |
+| ---------- | ------------------- | --- |
+| 2023-01-01 | 2023-01-01 23:58:41 | ... |
+| 2023-01-02 | 2023-01-02 00:03:17 | ... |
+| 2023-01-02 | 2023-01-02 00:14:02 | ... |
+
+With the table above, filtering only on `event` can be very innefective. For example, this query:
+
+```sql
+SELECT
+ *
+FROM
+ some_table
+WHERE
+ event BETWEEN '2023-01-02 00:00:00' AND '2023-01-02 01:00:00'
+```
+
+Would scan all the `ds` partitions, even though only one is needed! By setting the attribute to true, if `ds` is set as the main datetime column then the query would be generated as:
+
+```sql
+SELECT
+ *
+FROM
+ some_table
+WHERE
+ event BETWEEN '2023-01-02 00:00:00' AND '2023-01-02 01:00:00' AND
+ ds BETWEEN '2023-01-02 00:00:00' AND '2023-01-02 01:00:00'
+```
+
+Which reads data from a single partition instead.
+
+### `time_groupby_inline = False`
+
+In theory this attribute should be used to ommit time filters from the self-joins. When the attribute is false the time attribute will be present in the subquery used to compute limited series, eg:
+
+```sql
+SELECT DATE_TRUNC('day', ts) AS ts,
+ team AS team,
+ COUNT(*) AS count
+FROM public.threads
+JOIN
+ (SELECT team AS team__,
+ COUNT(*) AS mme_inner__
+ FROM public.threads
+ -- this is added when `time_groupby_inline = False`
+ WHERE ts >= TO_TIMESTAMP('2022-07-27 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US')
+ AND ts < TO_TIMESTAMP('2023-07-27 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US')
+ --
+ GROUP BY team
+ ORDER BY mme_inner__ DESC
+ LIMIT 5) AS anon_1 ON team = team__
+WHERE ts >= TO_TIMESTAMP('2022-07-27 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US')
+ AND ts < TO_TIMESTAMP('2023-07-27 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SS.US')
+GROUP BY DATE_TRUNC('day', ts),
+ team
+ORDER BY count DESC
+LIMIT 10000;
+```
+
+In practice, the attribute doesn't seem to be working as of 2023-07-27.
+
+### `allows_alias_to_source_column = True`
+
+When this is true the database allows queries where alias can overshadow existing column names. For example, in this query:
+
+```sql
+SELECT
+ foo + 1 AS foo
+FROM
+ some_table
+ORDER BY
+ foo -- references the alias `foo + 1`, not the column `foo`
+```
+
+### `allows_hidden_orderby_agg = True`
+
+If set to true the database allows expressions in the `GROUP BY` that are not present in the projection (`SELECT`), eg:
+
+```sql
+SELECT
+ country,
+ COUNT(*)
+FROM
+ some_table
+GROUP BY
+ country
+ORDER BY
+ SUM(population) -- not present in the `SELECT`
+```
+
+### `allows_hidden_cc_in_orderby = False`
+
+This the opposite of `allows_alias_in_orderby`, for databases that require aliases in the `ORDER BY`. For example, BigQuery doesn't like this query:
+
+```sql
+SELECT
+ CASE
+ WHEN type = 'feature' THEN 'f'
+ WHEN type = 'bug' THEN 'b'
+ ELSE 'o'
+ END AS cc_type
+FROM
+ some_table
+GROUP BY
+ cc_type
+ORDER BY
+ CASE
+ WHEN type = 'feature' THEN 'f'
+ WHEN type = 'bug' THEN 'b'
+ ELSE 'o'
+ END
+```
+
+Instead, it must be written as:
+
+```sql
+SELECT
+ CASE
+ WHEN type = 'feature' THEN 'f'
+ WHEN type = 'bug' THEN 'b'
+ ELSE 'o'
+ END AS cc_type
+FROM
+ some_table
+GROUP BY
+ cc_type
+ORDER BY
+ cc_type
+```
+
+### `allows_cte_in_subquery = True`
+
+When a virtual dataset is used in a chart the original query is converted into a subquery, and is wrapped in an outer query that is generated based on the chart controls. The virtual dataset query might have a CTE, and some databases don't like subqueries with CTEs in them.
+
+When this attribute is false Superset will extract the CTE and move it outside of the subquery when generating SQL for charts. The name of the new CTE will be `cte_alias`, also defined in the DB engine spec.
+
+### `allow_limit_clause = True`
+
+Allows for the `LIMIT` clause. Otherwise, the database probably uses `TOP` to limit rows.
+
+### `max_column_name_length: int | None = None`
+
+Most databases have a well defined limit for the maximum length of a column name (SQLite is probably the one exception). While the can be set (and defaults) to `None,` it's highly recommended to set a value to prevent errors.
+
+### `allows_sql_comments = True`
+
+Are comments supported in the DB? In general SQL in comments are defined by double dashes:
+
+```sql
+-- this is a comment
+SELECT * -- we need everything
+FROM some_table
+```
+
+### `allows_escaped_colons = True`
+
+SQLAlchemy recommends escaping colons to prevent them from being interpreted as bindings to parameters. Because of this, when building queries from virtual datasets Superset will escape all colons with `\:`.
+
+This works for most databases except Athena. The `allows_escaped_colons` attribute specifies if the database supports the escape colon.
diff --git a/superset/db_engine_specs/lib.py b/superset/db_engine_specs/lib.py
index 48a5694f96..93db779c6e 100644
--- a/superset/db_engine_specs/lib.py
+++ b/superset/db_engine_specs/lib.py
@@ -17,14 +17,62 @@
from __future__ import annotations
-from typing import Any, List, Tuple, Type
+from typing import Any
from superset.constants import TimeGrain
from superset.db_engine_specs import load_engine_specs
from superset.db_engine_specs.base import BaseEngineSpec
-
-def has_custom_method(spec: Type[BaseEngineSpec], method: str) -> bool:
+LIMIT_METHODS = {
+ "FORCE_LIMIT": "modifies the query, replacing an existing LIMIT or adding a new one", # E: line too long (89 > 79 characters)
+ "WRAP_SQL": "wraps the original query in a SELECT * with a LIMIT",
+ "FETCH_MANY": "runs the query unmodified but fetchs only LIMIT rows from the cursor", # E: line too long (89 > 79 characters)
+}
+
+DATABASE_DETAILS = {
+ "limit_method": "Method used to limit the rows in the subquery",
+ "joins": "Supports JOINs",
+ "subqueries": "Supports subqueries",
+ "alias_in_select": "Allows aliases in the SELECT statement",
+ "alias_in_orderby": "Allows referencing aliases in the ORDER BY statement",
+ "secondary_time_columns": "Supports secondary time columns",
+ "time_groupby_inline": "Allows ommiting time filters from inline GROUP BYs", # E: line too long (80 > 79 characters)
+ "alias_to_source_column": "Able to use source column when an alias overshadows it", # E: line too long (87 > 79 characters)
+ "order_by_not_in_select": "Allows aggregations in ORDER BY not present in the SELECT", # E: line too long (90 > 79 characters)
+ "expressions_in_orderby": "Allows expressions in ORDER BY",
+ "cte_in_subquery": "Allows CTE as a subquery",
+ "limit_clause": "Allows LIMIT clause (instead of TOP)",
+ "max_column_name": "Maximum column name",
+ "sql_comments": "Allows comments",
+ "escaped_colons": "Colons must be escaped",
+}
+BASIC_FEATURES = {
+ "masked_encrypted_extra": "Masks/unmasks encrypted_extra",
+ "column_type_mapping": "Has column type mappings",
+ "function_names": "Returns a list of function names",
+}
+NICE_TO_HAVE_FEATURES = {
+ "user_impersonation": "Supports user impersonation",
+ "file_upload": "Support file upload",
+ "extra_table_metadata": "Returns extra table metadata",
+ "dbapi_exception_mapping": "Maps driver exceptions to Superset exceptions",
+ "custom_errors": "Parses error messages and returns Superset errors",
+ "dynamic_schema": "Supports changing the schema per-query",
+ "catalog": "Supports catalogs",
+ "dynamic_catalog": "Supports changing the catalog per-query",
+ "ssh_tunneling": "Can be connected thru an SSH tunnel",
+ "query_cancelation": "Allows query to be canceled",
+ "get_metrics": "Returns additional metrics on dataset creation",
+ "where_latest_partition": "Supports querying the latest partition only",
+}
+ADVANCED_FEATURES = {
+ "expand_data": "Expands complex types (arrays, structs) into rows/columns",
+ "query_cost_estimation": "Supports query cost estimation",
+ "sql_validation": "Supports validating SQL before running query",
+}
+
+
+def has_custom_method(spec: type[BaseEngineSpec], method: str) -> bool:
"""
Check if a class has a custom implementation of a method.
@@ -39,7 +87,7 @@ def has_custom_method(spec: Type[BaseEngineSpec], method: str) -> bool:
)
-def diagnose(spec: Type[BaseEngineSpec]) -> dict[str, Any]:
+def diagnose(spec: type[BaseEngineSpec]) -> dict[str, Any]:
"""
Run basic diagnostics on a given DB engine spec.
"""
@@ -145,26 +193,27 @@ def diagnose(spec: Type[BaseEngineSpec]) -> dict[str, Any]:
return output
-def get_name(spec: Type[BaseEngineSpec]) -> str:
+def get_name(spec: type[BaseEngineSpec]) -> str:
"""
Return a name for a given DB engine spec.
"""
return spec.engine_name or spec.engine
-def generate_table() -> List[Tuple[Any, ...]]:
+def generate_table() -> list[list[Any]]:
"""
Generate a table showing info for all DB engine specs.
-
- Data is returned as a list of tuples, appropriate to write to a CSV file.
"""
info = {}
for spec in sorted(load_engine_specs(), key=get_name):
info[get_name(spec)] = diagnose(spec)
+ # remove 3rd party DB engine specs
+ info = {k: v for k, v in info.items() if v["module"].startswith("superset")}
+
rows = []
- rows.append(tuple(info)) # header row
- rows.append(tuple(db_info["module"] for db_info in info.values()))
+ rows.append(["Feature"] + list(info)) # header row
+ rows.append(["Module"] + list(db_info["module"] for db_info in info.values()))
# descriptive
keys = [
@@ -185,12 +234,15 @@ def generate_table() -> List[Tuple[Any, ...]]:
"escaped_colons",
]
for key in keys:
- rows.append(tuple(db_info[key] for db_info in info.values()))
+ rows.append(
+ [DATABASE_DETAILS[key]] + list(db_info[key] for db_info in info.values())
+ )
# basic
for time_grain in TimeGrain:
rows.append(
- tuple(db_info["time_grains"][time_grain.name] for db_info in info.values())
+ [f"Has time grain {time_grain.name}"]
+ + list(db_info["time_grains"][time_grain.name] for db_info in info.values())
)
keys = [
"masked_encrypted_extra",
@@ -198,7 +250,9 @@ def generate_table() -> List[Tuple[Any, ...]]:
"function_names",
]
for key in keys:
- rows.append(tuple(db_info[key] for db_info in info.values()))
+ rows.append(
+ [BASIC_FEATURES[key]] + list(db_info[key] for db_info in info.values())
+ )
# nice to have
keys = [
@@ -216,7 +270,10 @@ def generate_table() -> List[Tuple[Any, ...]]:
"where_latest_partition",
]
for key in keys:
- rows.append(tuple(db_info[key] for db_info in info.values()))
+ rows.append(
+ [NICE_TO_HAVE_FEATURES[key]]
+ + list(db_info[key] for db_info in info.values())
+ )
# advanced
keys = [
@@ -225,23 +282,24 @@ def generate_table() -> List[Tuple[Any, ...]]:
"sql_validation",
]
for key in keys:
- rows.append(tuple(db_info[key] for db_info in info.values()))
+ rows.append(
+ [ADVANCED_FEATURES[key]] + list(db_info[key] for db_info in info.values())
+ )
- rows.append(tuple(db_info["score"] for db_info in info.values()))
+ rows.append(["Score"] + list(db_info["score"] for db_info in info.values()))
return rows
if __name__ == "__main__":
- import csv
-
from superset.app import create_app
app = create_app()
with app.app_context():
rows = generate_table()
- with open("db_engine_specs.csv", "w", encoding="utf-8") as fp:
- writer = csv.writer(fp, delimiter="\t")
- for row in rows:
- writer.writerow(row)
+ headers = rows.pop(0)
+ print("| " + " | ".join(headers) + " |")
+ print("| " + " ---| " * len(headers))
+ for row in rows:
+ print("| " + " | ".join(str(col) for col in row) + " |")
diff --git a/superset/db_engine_specs/postgres.py b/superset/db_engine_specs/postgres.py
index 642f84f58c..e6c8f0a569 100644
--- a/superset/db_engine_specs/postgres.py
+++ b/superset/db_engine_specs/postgres.py
@@ -101,6 +101,8 @@ class PostgresBaseEngineSpec(BaseEngineSpec):
engine = ""
engine_name = "PostgreSQL"
+ time_groupby_inline = True
+
supports_catalog = True
_time_grain_expressions = {