Module tripleblind.report_asset
Specialized Asset representing a parameterized database report.
The ReportAsset wraps a generic database query, providing a mechanism to publish a limited query (such as an SQL database query) with very tightly controlled options. These ReportAssets can then be easily executed by others to perform the limited variety of queries upon demand. Like all other Assets, the report can be manually approved or access can be granted via an Agreement.
Reports only allow changes to the limited parameters and validate provided parameters before usage, ensuring the report consumer can only utilize the intended range of options. This enables powerful but strongly controlled reporting against even the most sensitive of databases.
Parameters must be an integer, float or a string from an explicit list of options. Multiple parameters can be used by a report, including optional values and default. The parameters are used by the query_template, utilizing the Mustache templating language (see https://mustache.github.io).
Connection strings can be templated using Mustache as well, allowing for secrets to be used in the connection string. For example, a connection string could be defined as: "mssql+pyodbc://{{secret_username}}:{{secret_password}}@myserver:3306/payroll" Or secrets could be included in the parameters: username="{{secret_username}}", password="{{secret_password}}" if using a create method which accepts those parameters. See "Using Named Secrets" under https://deveval.tripleblind.app/portal/docs/user-guide/asset-owner-operations for more details.
Example report definition:
demographic_param = ReportParameter.create_string(
name="demographic",
description="Specific demographic to filter the report on",
options=[
ParameterOption("Gender"),
ParameterOption("Ethnicity"),
],
)
query_template = '''
select Dept_Name, {{demographic}}, avg(Total_Gross) as average_Pay
from tripleblind_datasets.city_of_somerville_payroll
group by Dept_Name, {{demographic}};
'''
report = tb.report_asset.BigQueryReport.create(
gcp_project_id,
bigquery_dataset,
credentials,
query_template=query_template,
params=[demographic_param],
name=f"EXAMPLE- Payroll Report",
)
Example report usage:
# Run a payroll report summarizing by Gender.
report = tb.ReportAsset.find("EXAMPLE- Payroll Report")
print(report.run({"demographic": "Gender"}).raw_content)
Classes
class BigQueryReport (uuid: UUID, query_template: str = '', params: List[preprocessor.report_parameters.ReportParameter] = typing.List, connection: str = '', connection_opts: Optional[dict] = None)
-
A parameterized database report asset backed by a view from a BigQuery database.
Ancestors
Class variables
var connection : str
var connection_opts : Optional[dict]
var params : List[preprocessor.report_parameters.ReportParameter]
var query_template : str
Static methods
def create(gcp_project: str, bigquery_dataset: str, credentials: Union[str, pathlib.Path], query_template: str, params: List[preprocessor.report_parameters.ReportParameter], name: str, desc: str, is_discoverable: Optional[bool] = False, cost: Optional[int] = 1, allow_overwrite: Optional[bool] = False, session: Optional[Session] = None, strategy: Optional[str] = None, validate_sql: Optional[bool] = True) -> DatabaseReport
-
Creates a parameterized database report using a connection to a BigQuery database.
Args
gcp_project
:str
- The project name of your Google Cloud Project which will be used to cover any query access costs.
bigquery_dataset
:str
- The BigQuery dataset name.
credentials
:str
orPath
- The path of your keyfile.json. See the Google documentation for more details. These credentials will be stored securely on your Access Point; neither TripleBlind nor anyone using your dataset will have access to it.
query_template
:str
- The SQL query template defined using the Mustache templating language for generating the report.
params
:List[ReportParameter]
- List of ReportParameters defining the configurable report parameters.
name
:str
- Name of the new asset.
desc
:str
- Description of the new asset (can include markdown)
is_discoverable
:bool
, optional- Should this asset be listed in the Router index to be found and used by others?
cost
:int
, optional- Price for accessing this asset, in US cents. Default is $0.01. Note: This can be overridden for specific organizations with an Agreement.
allow_overwrite
:bool
, optional- If False an exception will be thrown if the asset name already exists. If True, an existing asset will be overwritten.
session
:Session
, optional- A connection session. If not specified, the default session is used.
strategy
:str
, optional- Upload strategy Use 'stream' to use a web socket, or 'post' to perform a simple post to position the asset. Default is 'stream'.
validate_sql
:bool
, optional- If True (the default) the query syntax is checked for common SQL syntax errors.
Raises
SystemExit
- SQL syntax errors were found in query.
Returns
DatabaseReport
- New DatabaseReport asset on the Router, or None on failure
Inherited members
DatabaseReport
:accesspoint_filename
activate_date
add_agreement
archive
cast
deactivate_date
delete
desc
download
filename
find
find_all
get_report_params
hash
is_active
is_discoverable
is_valid
k_grouping
list_agreements
metadata
name
namespace
organization
organization_id
position
publish_to_org
retrieve
run
upload
uuid
class DatabaseReport (uuid: UUID, query_template: str = '', params: List[preprocessor.report_parameters.ReportParameter] = typing.List, connection: str = '', connection_opts: Optional[dict] = None)
-
A parameterized database report asset backed by a view from a database.
Ancestors
Subclasses
Class variables
var connection : str
var connection_opts : Optional[dict]
var params : List[preprocessor.report_parameters.ReportParameter]
var query_template : str
Static methods
def cast(asset: Asset) -> DatabaseReport
-
Convert a generic Asset into a DatabaseReport
This should only be used on an asset known to be a DatabaseReport, no validation occurs during the cast.
Args
asset
:Asset
- A generic Asset
Returns
DatabaseReport
- A DatabaseReport object
def create(connection: str, query_template: str, params: List[preprocessor.report_parameters.ReportParameter], name: str, desc: str, connection_opts: Optional[dict] = None, credentials_info: Optional[dict] = None, is_discoverable: Optional[bool] = False, cost: Optional[int] = 1, allow_overwrite: Optional[bool] = False, session: Optional[Session] = None, strategy: Optional[str] = None, validate_sql: Optional[bool] = True) -> DatabaseReport
-
Creates a parameterized database report.
Args
connection
:str
- The connection string to the remote database connection. Secrets can be included in the connection string using Mustache templating of named secrets, e.g. "mssql+pyodbc://{{secret_username}}:{{secret_password}}@myserver.acme.com:3306/payroll". Any portion of the connection string can be templated, including the host, username, password, database, etc.
query_template
:str
- The SQL query template defined using the Mustache templating language for generating the report.
params
:List[ReportParameter]
- List of ReportParameters defining the configurable report parameters.
name
:str
- Name of the new asset.
desc
:str
- Description of the new asset (can include markdown).
connection_opts
:dict
, optional- Optional dictionary of database connection options.
credentials_info
:dict
, optional- Dictionary of credentials information if not provided in the connection string.
is_discoverable
:bool
, optional- Should this asset be listed in the Router index to be found and used by others?
cost
:int
, optional- Price for accessing this asset, in US cents. Default is $0.01. Note: This can be overridden for specific organizations with an Agreement.
allow_overwrite
:bool
, optional- If False an exception will be thrown if the asset name already exists. If True, an existing asset will be overwritten.
session
:Session
, optional- A connection session. If not specified, the default session is used.
strategy
:str
, optional- Upload strategy Use 'stream' to use a web socket, or 'post' to perform a simple post to position the asset. Default is 'stream'.
validate_sql
:bool
, optional- If True (the default) the query syntax is checked for common SQL syntax errors.
Raises
Exception
- Name must be unique for each parameter.
SystemExit
- SQL syntax errors were found in query.
Returns
DatabaseReport
- New DatabaseReport asset on the Router, or None on failure
Inherited members
class MSSQLReport (uuid: UUID, query_template: str = '', params: List[preprocessor.report_parameters.ReportParameter] = typing.List, connection: str = '', connection_opts: Optional[dict] = None)
-
A parameterized database report backed by a view of a Microsoft SQL database.
Ancestors
Class variables
var connection : str
var connection_opts : Optional[dict]
var params : List[preprocessor.report_parameters.ReportParameter]
var query_template : str
Static methods
def create(host: str, port: int, database: str, query_template: str, params: List[preprocessor.report_parameters.ReportParameter], name: str, desc: str, username: Optional[str] = None, password: Optional[str] = None, options: Optional[dict] = {}, is_discoverable: Optional[bool] = False, cost: Optional[int] = 1, allow_overwrite: Optional[bool] = False, session: Optional[Session] = None, strategy: Optional[str] = None, validate_sql: Optional[bool] = True) -> DatabaseReport
-
Creates a parameterized database report using a connection to a Microsoft SQL database.
Args
host
:str
- The host name of the Microsoft SQL database or a secret name. Example: testsqlserver123.database.windows.net
port
:int
- The port number of the Microsoft SQL database.
database
:str
- The name of the Microsoft SQL database to connect to or a secret name. Example: "dev" or "{{secret_database_name}}".
query_template
:str
- The SQL query template defined using the Mustache templating language for generating the report.
params
:List[ReportParameter]
- List of ReportParameters defining the configurable report parameters.
name
:str
- Name of the new asset.
desc
:str
- Description of the new asset (can include markdown)
username
:str
, optional- Username to use in the database connection, like "myuser" or a secret name like "{{secret_username}}".
password
:str
, optional- Password to use in the database connection or a secret name.
options
:dict
, optional- Dictionary of connection options for connecting to the Microsoft SQL database. For supported connection options see https://learn.microsoft.com/en-us/sql/connect/odbc/dsn-connection-string-attribute?view=sql-server-ver16#supported-dsnconnection-string-keywords-and-connection-attributes NOTE: The driver parameter is not required and the connection will use the access point's version of the driver. Example: options={ "authentication": "ActiveDirectoryMsi", }
is_discoverable
:bool
, optional- Should this asset be listed in the Router index to be found and used by others?
cost
:int
, optional- Price for accessing this asset, in US cents. Default is $0.01. Note: This can be overridden for specific organizations with an Agreement.
allow_overwrite
:bool
, optional- If False an exception will be thrown if the asset name already exists. If True, an existing asset will be overwritten.
session
:Session
, optional- A connection session. If not specified, the default session is used.
strategy
:str
, optional- Upload strategy Use 'stream' to use a web socket, or 'post' to perform a simple post to position the asset. Default is 'stream'.
validate_sql
:bool
, optional- If True (the default) the query syntax is checked for common SQL syntax errors.
Raises
SystemExit
- SQL syntax errors were found in query.
Returns
DatabaseDataset
- New asset on the Router, or None on failure
Inherited members
DatabaseReport
:accesspoint_filename
activate_date
add_agreement
archive
cast
deactivate_date
delete
desc
download
filename
find
find_all
get_report_params
hash
is_active
is_discoverable
is_valid
k_grouping
list_agreements
metadata
name
namespace
organization
organization_id
position
publish_to_org
retrieve
run
upload
uuid
class OracleReport (uuid: UUID, query_template: str = '', params: List[preprocessor.report_parameters.ReportParameter] = typing.List, connection: str = '', connection_opts: Optional[dict] = None)
-
A parameterized database report backed by a view of an Oracle database.
Ancestors
Class variables
var connection : str
var connection_opts : Optional[dict]
var params : List[preprocessor.report_parameters.ReportParameter]
var query_template : str
Static methods
def create(host: str, port: int, database: str, query_template: str, params: List[preprocessor.report_parameters.ReportParameter], name: str, desc: str, username: Optional[str] = None, password: Optional[str] = None, options: Optional[dict] = {}, is_discoverable: Optional[bool] = False, cost: Optional[int] = 1, allow_overwrite: Optional[bool] = False, session: Optional[Session] = None, strategy: Optional[str] = None, validate_sql: Optional[bool] = True) -> DatabaseReport
-
Creates a parameterized database report using a connection to an Oracle database.
Args
host
:str
- The host name of the Oracle database or a secret name. Example: testoracle123.database.us-east-1.amazonaws.com
port
:int
- The port number of the Oracle database. The port for most Oracle databases is 1521.
database
:str
- The name of the Oracle database to connect to or a secret name. Example: "dev" or "{{secret_database_name}}".
query_template
:str
- The SQL query template defined using the Mustache templating language for generating the report.
params
:List[ReportParameter]
- List of ReportParameters defining the configurable report parameters.
name
:str
- Name of the new asset.
desc
:str
- Description of the new asset (can include markdown)
username
:str
, optional- Username to use in the database connection, like "myuser" or a secret name like "{{secret_username}}".
password
:str
, optional- Password to use in the database connection or a secret name.
options
:dict
, optional- Dictionary of connection options for connecting to the Oracle database. For supported connection options see https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#connection-strings Example: options={ "encoding": "UTF-8" }
is_discoverable
:bool
, optional- Should this asset be listed in the Router index to be found and used by others?
cost
:int
, optional- Price for accessing this asset, in US cents. Default is $0.01. Note: This can be overridden for specific organizations with an Agreement.
allow_overwrite
:bool
, optional- If False an exception will be thrown if the asset name already exists. If True, an existing asset will be overwritten.
session
:Session
, optional- A connection session. If not specified, the default session is used.
strategy
:str
, optional- Upload strategy Use 'stream' to use a web socket, or 'post' to perform a simple post to position the asset. Default is 'stream'.
validate_sql
:bool
, optional- If True (the default) the query syntax is checked for common SQL syntax errors.
Raises
SystemExit
- SQL syntax errors were found in query.
Returns
DatabaseDataset
- New asset on the Router, or None on failure
Inherited members
DatabaseReport
:accesspoint_filename
activate_date
add_agreement
archive
cast
deactivate_date
delete
desc
download
filename
find
find_all
get_report_params
hash
is_active
is_discoverable
is_valid
k_grouping
list_agreements
metadata
name
namespace
organization
organization_id
position
publish_to_org
retrieve
run
upload
uuid
class RedshiftReport (uuid: UUID, query_template: str = '', params: List[preprocessor.report_parameters.ReportParameter] = typing.List, connection: str = '', connection_opts: Optional[dict] = None)
-
A parameterized database report backed by a view of a redshift database.
Ancestors
Class variables
var connection : str
var connection_opts : Optional[dict]
var params : List[preprocessor.report_parameters.ReportParameter]
var query_template : str
Static methods
def create(host: str, port: int, database: str, query_template: str, params: List[preprocessor.report_parameters.ReportParameter], name: str, desc: str, username: Optional[str] = None, password: Optional[str] = None, connection_opts: Optional[dict] = None, is_discoverable: Optional[bool] = False, cost: Optional[int] = 1, allow_overwrite: Optional[bool] = False, session: Optional[Session] = None, strategy: Optional[str] = None, validate_sql: Optional[bool] = True) -> DatabaseReport
-
Creates a parameterized database report using a connection to a Redshift database.
Args
host
:str
- The host name of the Redshift database or a secret name. Example: default.528.us-east-2.redshift-serverless.amazonaws.com
port
:int
- The port number of the Redshift database.
database
:str
- The name of the Redshift database to connect to or or a secret name. Example: "dev" or "{{secret_database_name}}".
query_template
:str
- The SQL query template defined using the Mustache templating language for generating the report.
params
:List[ReportParameter]
- List of ReportParameters defining the configurable report parameters.
name
:str
- Name of the new asset.
desc
:str
- Description of the new asset (can include markdown)
username
:str
, optional- Username to use in the database connection, like "myuser" or a secret name like "{{secret_username}}".
password
:str
, optional- Password to use in the database connection or a secret name.
connection_opts
:dict
, optional- Dictionary of connection options for connecting to the Redshift database. Supported options are described https://docs.aws.amazon.com/redshift/latest/mgmt/python-configuration-options.html. Example using IAM keys: options={ "iam": True, "access_key_id": "AKFCXNRSVRCFGMRQCAQR", "secret_access_key": "bEGzX7QnOb7eK9CRt4CV97n4e/bKOtQUFd9/pgIc" }
is_discoverable
:bool
, optional- Should this asset be listed in the Router index to be found and used by others?
cost
:int
, optional- Price for accessing this asset, in US cents. Default is $0.01. Note: This can be overridden for specific organizations with an Agreement.
allow_overwrite
:bool
, optional- If False an exception will be thrown if the asset name already exists. If True, an existing asset will be overwritten.
session
:Session
, optional- A connection session. If not specified, the default session is used.
strategy
:str
, optional- Upload strategy Use 'stream' to use a web socket, or 'post' to perform a simple post to position the asset. Default is 'stream'.
validate_sql
:bool
, optional- If True (the default) the query syntax is checked for common SQL syntax errors.
Raises
SystemExit
- SQL syntax errors were found in query.
Returns
DatabaseReport
- New DatabaseReport asset on the Router, or None on failure
Inherited members
DatabaseReport
:accesspoint_filename
activate_date
add_agreement
archive
cast
deactivate_date
delete
desc
download
filename
find
find_all
get_report_params
hash
is_active
is_discoverable
is_valid
k_grouping
list_agreements
metadata
name
namespace
organization
organization_id
position
publish_to_org
retrieve
run
upload
uuid
class ReportAsset (uuid: UUID)
-
An abstract ReportAsset for representing a parameterizable query.
Ancestors
Subclasses
Static methods
def cast(asset: Asset) -> ReportAsset
-
Convert a generic Asset into a ReportAsset
This should only be used on an asset known to be a Report, no validation occurs during the cast.
Args
asset
:Asset
- A generic Asset
Returns
ReportAsset
- A ReportAsset object
def find(search: Union[str, re.Pattern, ForwardRef(None)], namespace: Optional[uuid.UUID] = None, owned: Optional[bool] = False, owned_by: Optional[int] = None, session: Optional[Session] = None, exact_match: Optional[bool] = True) -> Optional[ReportAsset]
-
Search the Router index for an asset matching the given search
Args
search
:str
orre.Pattern
, optional- Either an asset ID or a search pattern applied to asset names and descriptions. A simple string will match a substring or the entire string if exact_match is True, or a regular expression can be passed for complex searches.
namespace
:UUID
, optional- The UUID of the user to which this asset belongs. None indicates any user, NAMESPACE_DEFAULT_USER indicates the current API user.
owned
:bool
, optional- Only return owned assets (either personally or by the current user's organization)
owned_by
:int
, optional- Only return owned assets owned by the given organization ID
session
:Session
, optional- A connection session. If not specified, the default session is used.
exact_match
:bool
, optional- When the 'search' is a string, setting this to True will perform an exact match. Ignored for regex patterns, defaults to True.
Raises
TripleblindAssetError
- Thrown when multiple assets are found which match the search.
Returns
ReportAsset
- A single asset, or None if no match found
Methods
def get_report_params(self) -> List[preprocessor.report_parameters.ReportParameter]
-
Get ReportParameters which can be supplied to the ReportAsset.
Returns
List[ReportParameter]
- The list of configurable report parameters.
def run(self, params: Dict[str, Union[str, float, int, List[str]]], silent: bool = False, job_name: Optional[str] = None, session: Optional[Session] = None) -> Optional[TableAsset]
-
Execute the report using the given parameters
Args
params
:Dict[str, Union[str, float, int, List[str]]]
- A dictionary of parameter names and the value to be used in the report.
silent
:bool
, optional- Suppress status messages during execution? Default is to show messages.
job_name
:Optional[str]
, optional- The name associate with the job. Default name is "Blind Report - ASSET_NAME".
session
:Optional[Session]
, optional- A connection session. If not specified, the default session is used.
Raises
TripleblindReportError
- Report failed to run
Returns
TableAsset
- The generated output, or None if the report fails
Inherited members
class SnowflakeReport (uuid: UUID, query_template: str = '', params: List[preprocessor.report_parameters.ReportParameter] = typing.List, connection: str = '', connection_opts: Optional[dict] = None)
-
A parameterized database report asset backed by a view from a Snowflake database.
Ancestors
Class variables
var connection : str
var connection_opts : Optional[dict]
var params : List[preprocessor.report_parameters.ReportParameter]
var query_template : str
Static methods
def create(snowflake_username: str, snowflake_password: str, snowflake_account: str, snowflake_warehouse: str, snowflake_database: str, snowflake_schema: str, role: str, query_template: str, params: List[preprocessor.report_parameters.ReportParameter], name: str, desc: str, is_discoverable: Optional[bool] = False, cost: Optional[int] = 1, allow_overwrite: Optional[bool] = False, session: Optional[Session] = None, strategy: Optional[str] = None, validate_sql: Optional[bool] = True) -> DatabaseReport
-
Creates a parameterized database report using a connection to a Snowflake database
Args
snowflake_username
:str
- Your Snowflake username, like "myuser" or a secret name like "{{secret_username}}".
snowflake_password
:str
- Your Snowflake password or a secret name like "{{secret_password}}".
snowflake_account
:str
- You Snowflake account or a secret name. This is the start of the URL when you visit your console. For example, if the URL is https://ab12345.us-central1.gcp.snowflakecomputing.com/ then your snowflake_account is "ab12345.us-central1.gcp".
snowflake_warehouse
:str
- The name of the Snowflake warehouse you are connecting to for the query or a secret name.
snowflake_database
:str
- The name of the Snowflake database you are connecting to for the query or a secret name.
snowflake_schema
:str
- The name of the Snowflake schema you are connecting to for the query or a secret name.
role
:str
- The role of the Snowflake user you are using to connect to the Snowflake database or a secret name.
query_template
:str
- The SQL query template defined using the Mustache templating language for generating the report.
params
:List[ReportParameter]
- List of ReportParameters defining the configurable report parameters.
name
:str
- Name of the new asset.
desc
:str
- Description of the new asset (can include markdown)
is_discoverable
:bool
, optional- Should this asset be listed in the Router index to be found and used by others?
cost
:int
, optional- Price for accessing this asset, in US cents. Default is $0.01. Note: This can be overridden for specific organizations with an Agreement.
allow_overwrite
:bool
, optional- If False an exception will be thrown if the asset name already exists. If True, an existing asset will be overwritten.
session
:Session
, optional- A connection session. If not specified, the default session is used.
strategy
:str
, optional- Upload strategy Use 'stream' to use a web socket, or 'post' to perform a simple post to position the asset. Default is 'stream'.
validate_sql
:bool
, optional- If True (the default) the query syntax is checked for common SQL syntax errors.
Raises
SystemExit
- SQL syntax errors were found in query.
Returns
DatabaseReport
- New DatabaseReport asset on the Router, or None on failure
Inherited members
DatabaseReport
:accesspoint_filename
activate_date
add_agreement
archive
cast
deactivate_date
delete
desc
download
filename
find
find_all
get_report_params
hash
is_active
is_discoverable
is_valid
k_grouping
list_agreements
metadata
name
namespace
organization
organization_id
position
publish_to_org
retrieve
run
upload
uuid