Query

exception analyze.query.QueryDecodeError
analyze.query.allocate(project_id, source_query, driver_query, source_target_columns, driver_target_columns, target_frame_id, append)

Performs an allocation based on the provided sqlalchemy source and driver data queries

Parameters

  • project_id (str) – Unique project identifier

  • source_query (str) – Base64 encoded pickled sqlalchemy query for source data

  • driver_query (str) – Base64 encoded pickled sqlalchemy query for driver data

  • source_target_columns (dict) – Target columns for the allocation source data

  • driver_target_columns (dict) – Target columns for the driver data

  • target_frame_id (str) – Unique target table identifier

  • append (bool) – Append results to target table

Returns

None

analyze.query.delete(project_id, delete_query, recreate=False)

Performs a table delete

Parameters

  • project_id (str) – Unique project identifier

  • delete_query (str) – Base64 encoded pickled sqlalchemy query for delete statement

  • recreate (bool) – Whether to recreate the table (prevent increase in size)

Returns

None

analyze.query.dialect()

Returns the current datastore dialect, for use with sqlalchemy

Returns

The dialect of the database backend (‘postgresql’, ‘greenplum’ or ‘hana’)

Return type

str

analyze.query.download_archive(project_id, model_id, table_name)

Generates a table archive download from the specified table_name

Parameters

  • project_id (str) – Unique project identifier

  • model_id (str) –

  • table_name (str) –

Returns

None

download_csv(project_id, project_id, table_name=None, query=None, params=None, encoding='UTF8')

Generates a CSV download stream from the specified query.

Parameters

  • project_id (str) – Unique project identifier

  • table_name (str) – The fully-qualified name (i.e. “schema”.”table”) of the table to download.

  • query (str) – The query to execute. If params is set, this value is assumed to be a query template.

  • params (str) – The parameter collection (if any) to combine with the query template.

  • encoding (str) – The character encoding for the database to use when exporting data as CSV.

Returns

Entire result set as CSV, streamed using chunked transfer encoding.

Notes

query takes precedence over table_name. In other words, if both are specified, query will be used.

analyze.query.download_typed_csv(project_id, query, delimiter, quote_char, escape_char)

Generates a data typed CSV download from the specified query

Parameters

  • project_id (str) – Unique project identifier

  • query (str) – The query to execute. The result will be what is output to CSV

  • delimiter (str) – What to use as a delimiter

  • quote_char (str) – What to use as a quote character

  • escape_char (str) – What to use as an escape character

Returns

The download URI, in the uri keword of a dict

Return type

dict

analyze.query.download_xls(project_id, query, params=None, sheetname='sheet1', headers=True, types=None)

Generates a .xls file download from the specified query

Parameters

  • project_id (str) – Unique project identifier

  • query (str) – The query to execute. The results will be wrtten to the xls file

  • params (dict) – Excel publishing parameters

  • sheetname (str, optional) – What to name the worksheet

  • headers (bool, optional) – Should headers be included in the xls file?

  • types (str) – Types

Returns

The download URI, in the uri keword of a dict

Return type

dict

analyze.query.download_xlsx(project_id, query, params=None, sheetname='sheet1', headers=True, types=None)

Generates a .xlsx file download from the specified query

Parameters

  • project_id (str) – Unique project identifier

  • query (str) – The query to execute. The results will be wrtten to the xls file

  • params (dict) – Excel publishing parameters

  • sheetname (str, optional) – What to name the worksheet

  • headers (bool, optional) – Should headers be included in the xls file?

  • types (str) – Types

Returns

The download URI, in the uri keword of a dict

Return type

dict

analyze.query.facets(project_id, query, params=None)

Generates table facet information by column

Parameters

  • project_id (str) – Unique project identifier

  • query (str) – The query to execute. Facets will be determined based on the results of this query.

  • params (dict) – Publishing parameters

Returns

A dict containing facets for each column

Return type

dict

analyze.query.melt(project_id, source_query, target_frame_id, id_vars, value_vars=None, value_name='value', var_name='variable')

Performs an table unpivot/melt based on the provided sqlalchemy source query

Parameters

  • project_id (str) – Unique project identifier

  • source_query (str) – Base64 encoded pickled sqlalchemy query for source data

  • target_frame_id (str) – The target table for the result of the melt

  • id_vars (list) – Base64 encoded pickled sqlalchemy query for driver data

  • value_vars (list, optional) – Target columns for the allocation source data

  • value_name (str) – Target columns for the driver data

  • var_name (str) – Target columns for the driver data

Returns

None

analyze.query.pivot(project_id, source_query, category_column, value_column, target_frame_id)

Performs an table pivot based on the provided sqlalchemy source, given the columns for headers and values

Parameters

  • project_id (str) – Unique project identifier

  • source_query (str) – Base64 encoded pickled sqlalchemy query for source data

  • category_column (str) – Column to transform into column headers

  • value_column (str) – Column to use for values

  • agg_function (str) – Aggregation method

  • target_frame_id (str) – Unique target table identifier

Returns

None

analyze.query.query(project_id, query, params=None, max_rows=None)

Executes the specified query and returns the results

Parameters

  • project_id (str) – Unique project identifier

  • query (str) – The data-returning query to execute

  • params (dict, optional) – The query parameters

  • max_rows (int, optional) – The maximum number of rows to return

  • is_encoded (bool, optional) – If the query is and encoded, pickled SQLAlchemy object

Returns

The results of the query, represented as a list of dicts

Return type

list

analyze.query.query_config(project_id, config)

Builds a query based on the supplied config, executes the query and returns the results

Parameters

  • project_id (str) – Unique project identifier

  • config (dict) – The data-returning query to execute

Returns

The results of the query, represented as a list of dicts

Return type

list

analyze.query.retrieve_dimension_value(project_id, table_id, value_column, filter_values, column_dimension_map, agg_function='sum')

Retrieves a summarized value from a table using dimensional roll-ups and other specified filters. Similar to an MDX query retrieving a single value.

Parameters

  • project_id (str) – Unique project identifier

  • query (str) – The query to execute. The result will be what is output to CSV

  • table_id (str) – Unique table or view identifier

  • value_column (str) – Columns to retrieve value

  • filter_values (dict) – Map of columns (key) and filter values (value) to set for query

  • column_dimension_map (dict) – Map of columns (key) to unique dimension IDs (dict with name and hierarchy keys). If a column is mapped to a dimension, the filter_value will be treated as a node. If hierarchy key is omitted, defaults to ‘main’.

  • agg_function (str) – Aggregation method for value determination. Options are sum, mean, median, std, var, min, and max.

Returns

Summarized column value

Return type

Decimal

analyze.query.table(project_id, table_id, version_id=None)

Provides detailed table information

Parameters

  • project_id (str) – Unique project identifier

  • table_id (str) – Unique table identifier

  • version_id – version??

Returns

dict with the following properties

  • meta (dict): Table and query meta data

  • rows (list): Data rows

Return type

dict

analyze.query.update(project_id, update_query)

Performs a table update

Parameters

  • project_id (str) – Unique project identifier

  • update_query (str) – Base64 encoded pickled sqlalchemy query for update statement

Returns

None

analyze.query.upsert(project_id, update_query, insert_query, recreate=False)

Performs a table upsert

Parameters

  • project_id (str) – Unique project identifier

  • update_query (str) – Base64 encoded pickled sqlalchemy query for update statement

  • insert_query (str) – Base64 encoded pickled sqlalchemy query for insert statement

  • recreate (bool) – Whether to recreate the table (prevent increase in size)

Returns

None