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 or Path
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

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

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

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

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 or re.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