> ## Documentation Index > Fetch the complete documentation index at: https://docs.kinetica.com/llms.txt > Use this file to discover all available pages before exploring further. # gpudbtable > gpudbtable — Kinetica Python API

Class GPUdbTable

class gpudb.GPUdbTable(\_type=None, name=None, options=None, db=None, read\_only\_table\_count=None, delete\_temporary\_views=True, temporary\_view\_names=None, create\_views=True, use\_multihead\_io=False, use\_multihead\_ingest=False, multihead\_ingest\_batch\_size=10000, flush\_multi\_head\_ingest\_per\_insertion=False, convert\_special\_types\_on\_retrieval=False)

Parameters

\_type (RecordType or GPUdbRecordType or list of lists of str) –

Either a GPUdbRecordType or RecordType object which represents a type for the table, or a nested list of lists, where each internal list has the format of:

``` # Just the name and type [ "name", "type (double, int etc.)" ] # Name, type, and one column property [ "name", "type (double, int etc.)", "nullable" ] # Name, type, and multiple column properties [ "name", "string", "char4", "nullable" ] ```

Pass None for collections. If creating a GPUdbTable object for a pre-existing table, then also pass None.

If no table with the given name exists, then the given type will be created in GPUdb before creating the table.

Default is None.

name (str) –

The name for the table. if none provided, then a random name will be generated using random\_name(). The name may contain the schema name (separated by a period). Alternatively, if the table name has no schema name but a collection name is specified via the options, that collection name will be treated as the schema name. Must not specify a schema name in this argument and specify a collection name also. The fully qualified version of the table name, i.e. ‘schema\_name.table\_name’ will be used for all endpoint calls internally.

options (GPUdbTableOptions or dict) –

A GPUdbTableOptions object or a dict containing options for the table creation.

db (GPUdb) –

A GPUdb object that allows the user to connect to the GPUdb server.

read\_only\_table\_count (int) –

For known read-only tables, provide the number of records in it. Integer. Must provide the name of the table.

delete\_temporary\_views (bool) –

If true, then in terminal queries–queries that can not be chained–delete the temporary views upon completion. Defaults to True.

create\_views (bool) –

Indicates whether or not to create views for this table.

temporary\_view\_names (list) –

Optional list of temporary view names (that ought to be deleted upon terminal queries)

use\_multihead\_io (bool) –

Indicates whether or not to use multi-head input and output (meaning ingestion and lookup). Default is False. Note that multi-head ingestion is more computation intensive for sharded tables, and it is probably advisable only if there is a heavy ingestion load. Choose carefully.

Please see documentation of parameters multihead\_ingest\_batch\_size and flush\_multi\_head\_ingest\_per\_insertion for controlling the multi-head ingestion related behavior.

use\_multihead\_ingest (bool) –

Indicates whether or not to use multi-head ingestion, if available upon insertion. Note that multi-head ingestion is more computation intensive for sharded tables, and it is probably advisable only if there is a heavy ingestion load. Default is False. Will be deprecated in version 7.0.

multihead\_ingest\_batch\_size (int) –

Used only in conjunction with use\_multihead\_ingest; ignored otherwise. Sets the batch size to be used for the ingestor. Must be greater than zero. Default is 10,000. The multi-head ingestor flushes the inserted records every multihead\_ingest\_batch\_size automatically, unless flush\_multi\_head\_ingest\_automatically is False. Any remaining records would have to be manually flushed using flush\_data\_to\_server() by the user, or will be automatically flushed per insert\_records() if flush\_multi\_head\_ingest\_automatically is True.

flush\_multi\_head\_ingest\_per\_insertion (bool) –

Used only in conjunction with use\_multihead\_ingest; ignored otherwise. If True, flushes the multi-head ingestor in every insert\_records() call. Otherwise, the multi-head ingestor flushes the data to the server when a worker queue reaches multihead\_ingest\_batch\_size in size, and any remaining records will have to be manually flushed using flush\_data\_to\_server(). Default False.

convert\_special\_types\_on\_retrieval (bool) –

Convert array types to list and JSON types to dicts while retrieval. Default False.

Returns

A GPUdbTable object.

static random\_name()

Returns a randomly generated UUID-based name. Use underscores instead of hyphens.

static prefix\_name(val)

Returns a random name with the specified prefix

size()

Return the table’s size/length/count.

set\_logger\_level(log\_level)

Set the log level for the GPUdbTable class and any multi-head i/o related classes it uses.

Parameters

log\_level (int or str) –

A valid log level for the logging module

property table\_name

Return user given name for this table (or the randomly generated one, if applicable).

property name

Return user given name for this table (or the randomly generated one, if applicable). Return the qualified version

property qualified\_table\_name

Return the fully qualified name for this table, including any schemas.

property is\_read\_only

Is the table read-only, or can we modify it?

property count

Return the table’s size/length/count.

property is\_collection

Returns True if the table is a collection; False otherwise.

property collection\_name

Returns the name of the collection this table is a member of; None if this table does not belong to any collection.

is\_replicated()

Returns True if the table is replicated.

get\_table\_type()

Return the table’s (record) type (the GPUdbRecordType object, not the c-extension RecordType).

alias(alias)

Create an alias string for this table.

Parameters

alias (str) –

A string that contains the alias.

Returns

A string with the format “this-table-name as alias”.

create\_view(view\_name, count=None)

Given a view name and a related response, create a new GPUdbTable object which is a read-only table with the intermediate tables automatically updated.

Returns

A GPUdbTable object

cleanup()

Clear/drop all intermediate tables if settings allow it.

Returns

self for enabling chaining method invocations.

exists(options={"{"}{"}"})

Checks for the existence of a table with the given name.

Returns

A boolean flag indicating whether the table currently –

exists in the database.

flush\_data\_to\_server()

If multi-head ingestion is enabled, then flush all records in the ingestors’ worker queues so that they actually get inserted to the server database.

static convert\_special\_type\_values\_in\_insert\_records(record\_type: GPUdbRecordType, record: list | dict)

Convert any special value types (array, json, vector etc.) of a single record suitably

Parameters

record\_type (GPUdbRecordType) –

the record type for the record

record (Union\[ list, dict]) –

A single record (list or dict)

Returns

object –

the record with converted values

static convert\_special\_type\_values\_in\_get\_records(record\_type: GPUdbRecordType, records: list | dict | List\[Record])

Convert any special value type (array, json, vector etc.) suitably

Parameters

record\_type (GPUdbRecordType) –

the record type for these ‘records’

records (Union\[Union\[ list, dict], List\[Record]]) –

A single record value (list or dict)

Returns

object –

the converted value

insert\_records(\*args, \*\*kwargs)

Insert one or more records.

Parameters

args –

Values for all columns of a single record or multiple records. For a single record, use either of the following syntaxes:

``` insert_records( 1, 2, 3 ) insert_records( [1, 2, 3] ) ```

For multiple records, use either of the following syntaxes:

``` insert_records( [ [1, 2, 3], [4, 5, 6] ] ) insert_records( [1, 2, 3], [4, 5, 6] ) ```

Also, the user can use keyword arguments to pass in values:

``` # For a record type with two integers named 'a' and 'b': insert_records( {"a": 1, "b": 1}, {"a": 42, "b": 32} ) # Also can use a list to pass the dicts insert_records( [ {"a": 1, "b": 1}, {"a": 42, "b": 32} ] ) ```

Additionally, the user may provide options for the insertion operation. For example:

``` insert_records( [1, 2, 3], [4, 5, 6], options = {"return_record_ids": "true"} ) ```
kwargs –

Values for all columns for a single record. Mutually exclusive with args (i.e. cannot provide both) when it only contains data.

May contain an ‘options’ keyword arg which will be passed to the database for the insertion operation.

Returns

A GPUdbTable object with the insert\_records() –

response fields converted to attributes and stored within.

insert\_records\_random(count=None, options={"{"}{"}"})

Generates a specified number of random records and adds them to the given table. There is an optional parameter that allows the user to customize the ranges of the column values. It also allows the user to specify linear profiles for some or all columns in which case linear values are generated rather than random ones. Only individual tables are supported for this operation.

This operation is synchronous, meaning that a response will not be returned until all random records are fully available.

Parameters

count (long) –

Number of records to generate.

options (dict of dicts of floats) –

Optional parameter to pass in specifications for the randomness of the values. This map is different from the options parameter of most other endpoints in that it is a map of string to map of string to doubles, while most others are maps of string to string. In this map, the top level keys represent which column’s parameters are being specified, while the internal keys represents which parameter is being specified. These parameters take on different meanings depending on the type of the column. Below follows a more detailed description of the map: Default value is an empty dict ( {"{"}{"}"} ). Allowed keys are:

  • seed – If provided, the internal random number generator will be initialized with the given value. The minimum is 0. This allows for the same set of random numbers to be generated across invocation of this endpoint in case the user wants to repeat the test. Since input parameter options, is a map of maps, we need an internal map to provide the seed value. For example, to pass 100 as the seed value through this parameter, you need something equivalent to: ‘options’ = {"{"}‘seed’: {"{"} ‘value’: 100 {"}"} {"}"} Allowed keys are:

    • value – Pass the seed value here.

  • all – This key indicates that the specifications relayed in the internal map are to be applied to all columns of the records. Allowed keys are:

    • min – For numerical columns, the minimum of the generated values is set to this value. Default is -99999. For point, shape, and track semantic types, min for numeric ‘x’ and ‘y’ columns needs to be within \[-180, 180] and \[-90, 90], respectively. The default minimum possible values for these columns in such cases are -180.0 and -90.0. For the ‘TIMESTAMP’ column, the default minimum corresponds to Jan 1, 2010. For string columns, the minimum length of the randomly generated strings is set to this value (default is 0). If both minimum and maximum are provided, minimum must be less than or equal to max. Value needs to be within \[0, 200]. If the min is outside the accepted ranges for strings columns and ‘x’ and ‘y’ columns for point/shape/track types, then those parameters will not be set; however, an error will not be thrown in such a case. It is the responsibility of the user to use the all parameter judiciously.

    • max – For numerical columns, the maximum of the generated values is set to this value. Default is 99999. For point, shape, and track semantic types, max for numeric ‘x’ and ‘y’ columns needs to be within \[-180, 180] and \[-90, 90], respectively. The default minimum possible values for these columns in such cases are 180.0 and 90.0. For string columns, the maximum length of the randomly generated strings is set to this value (default is 200). If both minimum and maximum are provided, max must be greater than or equal to min. Value needs to be within \[0, 200]. If the max is outside the accepted ranges for strings columns and ‘x’ and ‘y’ columns for point/shape/track types, then those parameters will not be set; however, an error will not be thrown in such a case. It is the responsibility of the user to use the all parameter judiciously.

    • interval – If specified, generate values for all columns evenly spaced with the given interval value. If a max value is specified for a given column the data is randomly generated between min and max and decimated down to the interval. If no max is provided the data is linearly generated starting at the minimum value (instead of generating random data). For non-decimated string-type columns the interval value is ignored. Instead the values are generated following the pattern: ‘attrname\_creationIndex#’, i.e. the column name suffixed with an underscore and a running counter (starting at 0). For string types with limited size (e.g., char4) the prefix is dropped. No nulls will be generated for nullable columns.

    • null\_percentage – If specified, then generate the given percentage of the count as nulls for all nullable columns. This option will be ignored for non-nullable columns. The value must be within the range \[0, 1.0]. The default value is 5% (0.05).

    • cardinality – If specified, limit the randomly generated values to a fixed set. Not allowed on a column with interval specified, and is not applicable to WKT or Track-specific columns. The value must be greater than 0. This option is disabled by default.

  • attr\_name – Set the following parameters for the column specified by the key. This overrides any parameter set by all. Allowed keys are:

    • min – For numerical columns, the minimum of the generated values is set to this value. Default is -99999. For point, shape, and track semantic types, min for numeric ‘x’ and ‘y’ columns needs to be within \[-180, 180] and \[-90, 90], respectively. The default minimum possible values for these columns in such cases are -180.0 and -90.0. For the ‘TIMESTAMP’ column, the default minimum corresponds to Jan 1, 2010. For string columns, the minimum length of the randomly generated strings is set to this value (default is 0). If both minimum and maximum are provided, minimum must be less than or equal to max. Value needs to be within \[0, 200]. If the min is outside the accepted ranges for strings columns and ‘x’ and ‘y’ columns for point/shape/track types, then those parameters will not be set; however, an error will not be thrown in such a case. It is the responsibility of the user to use the all parameter judiciously.

    • max – For numerical columns, the maximum of the generated values is set to this value. Default is 99999. For point, shape, and track semantic types, max for numeric ‘x’ and ‘y’ columns needs to be within \[-180, 180] and \[-90, 90], respectively. The default minimum possible values for these columns in such cases are 180.0 and 90.0. For string columns, the maximum length of the randomly generated strings is set to this value (default is 200). If both minimum and maximum are provided, max must be greater than or equal to min. Value needs to be within \[0, 200]. If the max is outside the accepted ranges for strings columns and ‘x’ and ‘y’ columns for point/shape/track types, then those parameters will not be set; however, an error will not be thrown in such a case. It is the responsibility of the user to use the all parameter judiciously.

    • interval – If specified, generate values for all columns evenly spaced with the given interval value. If a max value is specified for a given column the data is randomly generated between min and max and decimated down to the interval. If no max is provided the data is linearly generated starting at the minimum value (instead of generating random data). For non-decimated string-type columns the interval value is ignored. Instead the values are generated following the pattern: ‘attrname\_creationIndex#’, i.e. the column name suffixed with an underscore and a running counter (starting at 0). For string types with limited size (e.g., char4) the prefix is dropped. No nulls will be generated for nullable columns.

    • null\_percentage – If specified and if this column is nullable, then generate the given percentage of the count as nulls. This option will result in an error if the column is not nullable. The value must be within the range \[0, 1.0]. The default value is 5% (0.05).

    • cardinality – If specified, limit the randomly generated values to a fixed set. Not allowed on a column with interval specified, and is not applicable to WKT or Track-specific columns. The value must be greater than 0. This option is disabled by default.

  • track\_length – This key-map pair is only valid for track type data sets (an error is thrown otherwise). No nulls would be generated for nullable columns. Allowed keys are:

    • min – Minimum possible length for generated series; default is 100 records per series. Must be an integral value within the range \[1, 500]. If both min and max are specified, min must be less than or equal to max.

    • max – Maximum possible length for generated series; default is 500 records per series. Must be an integral value within the range \[1, 500]. If both min and max are specified, max must be greater than or equal to min.

Returns

A GPUdbTable object with the insert\_records() response –

fields converted to attributes (and stored within) –

following entries

table\_name (str) –

Value of input parameter table\_name.

count (long) –

Value of input parameter count.

get\_records\_by\_key(key\_values, expression='', options=None)

Fetches the record(s) from the appropriate worker rank directly (or, if multi-head record retrieval is not set up, then from the head node) that map to the given shard key.

Parameters

key\_values (list or dict) –

Values for the sharding columns of the record to fetch either in a list (then it is assumed to be in the order of the sharding keys in the record type) or a dict. Must not have any missing sharding/primary column value or any extra column values.

expression (str) –

Optional parameter. If given, it is passed to /get/records as a filter expression.

options (dict of str to str or None) –

Any /get/records options to be passed onto the GPUdb server. Optional parameter.

Returns

The decoded records.

get\_records(offset=0, limit=-9999, encoding='binary', options={"{"}{"}"}, force\_primitive\_return\_types=True)

Retrieves records from a given table, optionally filtered by an expression and/or sorted by a column. This operation can be performed on tables, views, or on homogeneous collections (collections containing tables of all the same type). Records can be returned encoded as binary or json.

This operation supports paging through the data via the input parameter offset and input parameter limit parameters. Note that when paging through a table, if the table (or the underlying table in case of a view) is updated (records are inserted, deleted or modified) the records retrieved may differ between calls based on the updates applied.

Decodes and returns the fetched records.

Parameters

offset (long) –

A positive integer indicating the number of initial results to skip (this can be useful for paging through the results). Default value is 0. The minimum allowed value is 0. The maximum allowed value is MAX\_INT.

limit (long) –

A positive integer indicating the maximum number of results to be returned. Or END\_OF\_SET (-9999) to indicate that the max number of results should be returned. Default value is -9999.

encoding (str) –

Specifies the encoding for returned records. Default value is ‘binary’. Allowed values are:

  • binary

  • json

The default value is ‘binary’.

options (dict of str) –

Default value is an empty dict ( {"{"}{"}"} ). Allowed keys are:

  • expression – Optional filter expression to apply to the table.

  • fast\_index\_lookup – Indicates if indexes should be used to perform the lookup for a given expression if possible. Only applicable if there is no sorting, the expression contains only equivalence comparisons based on existing tables indexes and the range of requested values is from \[0 to END\_OF\_SET]. The default value is true.

  • sort\_by – Optional column that the data should be sorted by. Empty by default (i.e. no sorting is applied).

  • sort\_order – String indicating how the returned values should be sorted - ascending or descending. If sort\_order is provided, sort\_by has to be provided. Allowed values are:

    • ascending

    • descending

    The default value is ‘ascending’.

force\_primitive\_return\_types (bool) –

If True, then OrderedDict objects will be returned, where string sub-type columns will have their values converted back to strings; for example, the Python datetime structs, used for datetime type columns would have their values returned as strings. If False, then Record objects will be returned, which for string sub-types, will return native or custom structs; no conversion to string takes place. String conversions, when returning OrderedDicts, incur a speed penalty, and it is strongly recommended to use the Record object option instead. If True, but none of the returned columns require a conversion, then the original Record objects will be returned. Default value is True.

Returns

A list of Record objects containing the record values.

get\_records\_by\_column(column\_names, offset=0, limit=-9999, encoding='binary', options={"{"}{"}"}, print\_data=False, force\_primitive\_return\_types=True, get\_column\_major=True)

For a given table, retrieves the values of the given columns within a given range. It returns maps of column name to the vector of values for each supported data type (double, float, long, int and string). This operation supports pagination feature, i.e. values that are retrieved are those associated with the indices between the start (offset) and end value (offset + limit) parameters (inclusive). If there are num\_points values in the table then each of the indices between 0 and num\_points-1 retrieves a unique value.

Note that when using the pagination feature, if the table (or the underlying table in case of a view) is updated (records are inserted, deleted or modified) the records or values retrieved may differ between calls (noncontiguous or overlap) based on the type of the update.

The response is returned as a dynamic schema. For details see: dynamic schemas documentation.

Parameters

column\_names (list of str) –

The list of column values to retrieve.

offset (long) –

A positive integer indicating the number of initial results to skip (this can be useful for paging through the results). The minimum allowed value is 0. The maximum allowed value is MAX\_INT.

limit (long) –

A positive integer indicating the maximum number of results to be returned (if not provided the default is -9999), or END\_OF\_SET (-9999) to indicate that the maximum number of results allowed by the server should be returned.

encoding (str) –

Specifies the encoding for returned records; either ‘binary’ or ‘json’. Default value is ‘binary’. Allowed values are:

  • binary

  • json

The default value is ‘binary’.

options (dict of str) –

Default value is an empty dict ( {"{"}{"}"} ). Allowed keys are:

  • expression – Optional filter expression to apply to the table.

  • sort\_by – Optional column that the data should be sorted by. Empty by default (i.e. no sorting is applied).

  • sort\_order – String indicating how the returned values should be sorted - ascending or descending. Default is ‘ascending’. If sort\_order is provided, sort\_by has to be provided. Allowed values are:

    • ascending

    • descending

    The default value is ‘ascending’.

  • order\_by – Comma-separated list of the columns to be sorted by; e.g. ‘timestamp asc, x desc’. The columns specified must be present in input parameter column\_names. If any alias is given for any column name, the alias must be used, rather than the original column name.

print\_data (bool) –

If True, print the fetched data to the console in a tabular format if the data is being returned in the column-major format. Default is False.

force\_primitive\_return\_types (bool) –

If True, then OrderedDict objects will be returned, where string sub-type columns will have their values converted back to strings; for example, the Python datetime structs, used for datetime type columns would have their values returned as strings. If False, then Record objects will be returned, which for string sub-types, will return native or custom structs; no conversion to string takes place. String conversions, when returning OrderedDicts, incur a speed penalty, and it is strongly recommended to use the Record object option instead. If True, but none of the returned columns require a conversion, then the original Record objects will be returned. Default value is True.

get\_column\_major (bool) –

Indicates if the decoded records will be transposed to be column-major or returned as is (row-major). Default value is True.

Decodes the fetched records and saves them in the response class in an attribute called data.

Returns

A dict of column name to column values for column-major data, or –

a list of Record objects for row-major data.

get\_records\_by\_series(world\_table\_name=None, offset=0, limit=250, encoding='binary', options={"{"}{"}"}, force\_primitive\_return\_types=True)

Retrieves the complete series/track records from the given input parameter world\_table\_name based on the partial track information contained in the input parameter table\_name.

This operation supports paging through the data via the input parameter offset and input parameter limit parameters.

In contrast to get\_records() this returns records grouped by series/track. So if input parameter offset is 0 and input parameter limit is 5 this operation would return the first 5 series/tracks in input parameter table\_name. Each series/track will be returned sorted by their TIMESTAMP column.

Parameters

world\_table\_name (str) –

Name of the table containing the complete series/track information to be returned for the tracks present in the input parameter table\_name. Typically this is used when retrieving series/tracks from a view (which contains partial series/tracks) but the user wants to retrieve the entire original series/tracks. Can be blank.

offset (int) –

A positive integer indicating the number of initial series/tracks to skip (useful for paging through the results). Default value is 0. The minimum allowed value is 0. The maximum allowed value is MAX\_INT.

limit (int) –

A positive integer indicating the maximum number of series/tracks to be returned. Or END\_OF\_SET (-9999) to indicate that the max number of results should be returned. Default value is 250.

encoding (str) –

Specifies the encoding for returned records; either ‘binary’ or ‘json’. Default value is ‘binary’. Allowed values are:

  • binary

  • json

The default value is ‘binary’.

options (dict of str) –

Optional parameters. Default value is an empty dict ( {"{"}{"}"} ).

force\_primitive\_return\_types (bool) –

If True, then OrderedDict objects will be returned, where string sub-type columns will have their values converted back to strings; for example, the Python datetime structs, used for datetime type columns would have their values returned as strings. If False, then Record objects will be returned, which for string sub-types, will return native or custom structs; no conversion to string takes place. String conversions, when returning OrderedDicts, incur a speed penalty, and it is strongly recommended to use the Record object option instead. If True, but none of the returned columns require a conversion, then the original Record objects will be returned. Default value is True.

Returns

A list of list of Record objects containing the record values. –

Each external record corresponds to a single track (or series)

get\_records\_from\_collection(offset=0, limit=-9999, encoding='binary', options={"{"}{"}"}, force\_primitive\_return\_types=True)

Retrieves records from a collection. The operation can optionally return the record IDs which can be used in certain queries such as delete\_records().

This operation supports paging through the data via the input parameter offset and input parameter limit parameters.

Parameters

offset (long) –

A positive integer indicating the number of initial results to skip (this can be useful for paging through the results). Default value is 0. The minimum allowed value is 0. The maximum allowed value is MAX\_INT.

limit (long) –

A positive integer indicating the maximum number of results to be returned, or END\_OF\_SET (-9999) to indicate that the max number of results should be returned. Default value is -9999.

encoding (str) –

Specifies the encoding for returned records; either ‘binary’ or ‘json’. Default value is ‘binary’. Allowed values are:

  • binary

  • json

The default value is ‘binary’.

options (dict of str) –

Default value is an empty dict ( {"{"}{"}"} ). Allowed keys are:

  • return\_record\_ids – If ‘true’ then return the internal record ID along with each returned record. Default is ‘false’. Allowed values are:

    • true

    • false

    The default value is ‘false’.

force\_primitive\_return\_types (bool) –

If True, then OrderedDict objects will be returned, where string sub-type columns will have their values converted back to strings; for example, the Python datetime structs, used for datetime type columns would have their values returned as strings. If False, then Record objects will be returned, which for string sub-types, will return native or custom structs; no conversion to string takes place. String conversions, when returning OrderedDicts, incur a speed penalty, and it is strongly recommended to use the Record object option instead. If True, but none of the returned columns require a conversion, then the original Record objects will be returned. Default value is True.

Returns

A list of Record objects containing the record values.

get\_geo\_json(offset=0, limit=-9999, options={"{"}{"}"}, force\_primitive\_return\_types=True)

Retrieves records as a GeoJSON from a given table, optionally filtered by an expression and/or sorted by a column. This operation can be performed on tables, views, or on homogeneous collections (collections containing tables of all the same type). Records can be returned encoded as binary or json.

This operation supports paging through the data via the input parameter offset and input parameter limit parameters. Note that when paging through a table, if the table (or the underlying table in case of a view) is updated (records are inserted, deleted or modified) the records retrieved may differ between calls based on the updates applied.

Decodes and returns the fetched records.

Parameters

offset (long) –

A positive integer indicating the number of initial results to skip (this can be useful for paging through the results). Default value is 0. The minimum allowed value is 0. The maximum allowed value is MAX\_INT.

limit (long) –

A positive integer indicating the maximum number of results to be returned. Or END\_OF\_SET (-9999) to indicate that the max number of results should be returned. Default value is -9999.

encoding (str) –

Specifies the encoding for returned records. Default value is ‘binary’. Allowed values are:

  • binary

  • json

The default value is ‘binary’.

options (dict of str) –

Default value is an empty dict ( {"{"}{"}"} ). Allowed keys are:

  • expression – Optional filter expression to apply to the table.

  • fast\_index\_lookup – Indicates if indexes should be used to perform the lookup for a given expression if possible. Only applicable if there is no sorting, the expression contains only equivalence comparisons based on existing tables indexes and the range of requested values is from \[0 to END\_OF\_SET]. The default value is true.

  • sort\_by – Optional column that the data should be sorted by. Empty by default (i.e. no sorting is applied).

  • sort\_order – String indicating how the returned values should be sorted - ascending or descending. If sort\_order is provided, sort\_by has to be provided. Allowed values are:

    • ascending

    • descending

    The default value is ‘ascending’.

force\_primitive\_return\_types (bool) –

If True, then OrderedDict objects will be returned, where string sub-type columns will have their values converted back to strings; for example, the Python datetime structs, used for datetime type columns would have their values returned as strings. If False, then Record objects will be returned, which for string sub-types, will return native or custom structs; no conversion to string takes place. String conversions, when returning OrderedDicts, incur a speed penalty, and it is strongly recommended to use the Record object option instead. If True, but none of the returned columns require a conversion, then the original Record objects will be returned. Default value is True.

Returns

A GeoJSON object (a dict)

to\_df(\*\*kwargs)

Converts the table data to a Pandas Data Frame.

Parameters

batch\_size (int) –

The number of records to retrieve at a time from the database

Returns

A Pandas Data Frame containing the table data.

classmethod from\_df(df, db: GPUdb, table\_name: str, column\_types: dict = {"{"}{"}"}, clear\_table: bool = False, create\_table: bool = True, load\_data: bool = True, show\_progress: bool = False, batch\_size: int = 5000, \*\*kwargs)

Load a Data Frame into a table; optionally dropping any existing table, creating it if it doesn’t exist, and loading data into it; and then returning a GPUdbTable reference to the table.

Parameters

df (pd.DataFrame) –

The Pandas Data Frame to load into a table

db (GPUdb) –

GPUdb instance

table\_name (str) –

Name of the target Kinetica table for the Data Frame loading

column\_types (dict) –

Optional Kinetica column properties to apply to the column type definitions inferred from the Data Frame; map of column name to a list of column properties for that column, excluding the inferred base type. For example:

``` { "middle_name": [ 'char64', 'nullable' ], "state": [ 'char2', 'dict' ] } ```
clear\_table (bool) –

Whether to drop an existing table of the same name or not before creating this one.

create\_table (bool) –

Whether to create the table if it doesn’t exist or not.

load\_data (bool) –

Whether to load data into the target table or not.

show\_progress (bool) –

Whether to show progress of the operation on the console.

batch\_size (int) –

The number of records at a time to load into the target table.

Raises

GPUdbException

Returns

GPUdbTable –

a GPUdbTable instance created from the Data Frame passed in

type\_as\_df()

Return table columns as a dataframe for inspection.

insert\_df(df, \*\*kwargs)

Insert into a GPUdbTable from a dataframe.

static create\_join\_table(db, join\_table\_name=None, table\_names=None, column\_names=None, expressions=\[], options={"{"}{"}"})

Creates a table that is the result of a SQL JOIN.

For join details and examples see: Joins. For limitations, see Join Limitations and Cautions.

Parameters

join\_table\_name (str) –

Name of the join table to be created, in \[schema\_name.]table\_name format, using standard name resolution rules and meeting table naming criteria.

table\_names (list of str) –

The list of table names composing the join, each in \[schema\_name.]table\_name format, using standard name resolution rules. Corresponds to a SQL statement FROM clause. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

column\_names (list of str) –

List of member table columns or column expressions to be included in the join. Columns can be prefixed with ‘table\_id.column\_name’, where ‘table\_id’ is the table name or alias. Columns can be aliased via the syntax ‘column\_name as alias’. Wild cards ‘\*’ can be used to include all columns across member tables or ‘table\_id.\*’ for all of a single table’s columns. Columns and column expressions composing the join must be uniquely named or aliased–therefore, the ‘\*’ wild card cannot be used if column names aren’t unique across all tables. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

expressions (list of str) –

An optional list of expressions to combine and filter the joined tables. Corresponds to a SQL statement WHERE clause. For details see: expressions. The default value is an empty list ( \[] ). The user can provide a single element (which will be automatically promoted to a list internally) or a list.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter join\_table\_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_join\_table\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the join as part of input parameter join\_table\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema for the join. If the schema is non-existent, it will be automatically created. The default value is ‘’.

  • max\_query\_dimensions – No longer used.

  • strategy\_definition – The tier strategy for the table and its columns.

  • ttl – Sets the TTL of the join table specified in input parameter join\_table\_name.

  • view\_id – View this projection is part of. The default value is ‘’.

  • no\_count – Return a count of 0 for the join table for logging and for GPUdbTable.show\_table(); optimization needed for large overlapped equi-join stencils. The default value is ‘false’.

  • chunk\_size – Maximum number of records per joined-chunk for this table. Defaults to the gpudb.conf file chunk size.

  • enable\_virtual\_chunking – Collect chunks with accumulated size less than chunk\_size into a single chunk. The default value is ‘false’.

  • max\_virtual\_chunk\_size – Maximum number of records per virtual-chunk. When set, enables virtual chunking. Defaults to chunk\_size if virtual chunking otherwise enabled.

  • min\_virtual\_chunk\_size – Minimum number of records per virtual-chunk. When set, enables virtual chunking. Defaults to chunk\_size if virtual chunking otherwise enabled.

  • enable\_sparse\_virtual\_chunking – Materialize virtual chunks with only non-deleted values. The default value is ‘false’.

  • enable\_equi\_join\_lazy\_result\_store – Allow using the lazy result store to cache computation of one side of a multichunk equi-join. Reduces computation but also reduces parallelism to the number of chunks on the other side of the equi-join.

  • enable\_predicate\_equi\_join\_lazy\_result\_store – Allow using the lazy result store to cache computation of one side of a multichunk predicate-equi-join. Reduces computation but also reduces parallelism to the number of chunks on the other side of the equi-join.

  • enable\_pk\_equi\_join – Use equi-join to do primary key joins rather than using primary key index.

The default value is an empty dict ( {"{"}{"}"} ).

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

static create\_union(db, table\_name=None, table\_names=None, input\_column\_names=None, output\_column\_names=None, options={"{"}{"}"})

Merges data from one or more tables with comparable data types into a new table.

The following merges are supported:

UNION (DISTINCT/ALL) - For data set union details and examples, see Union. For limitations, see Union Limitations and Cautions.

INTERSECT (DISTINCT/ALL) - For data set intersection details and examples, see Intersect. For limitations, see Intersect Limitations.

EXCEPT (DISTINCT/ALL) - For data set subtraction details and examples, see Except. For limitations, see Except Limitations.

Parameters

table\_name (str) –

Name of the table to be created, in \[schema\_name.]table\_name format, using standard name resolution rules and meeting table naming criteria.

table\_names (list of str) –

The list of table names to merge, in \[schema\_name.]table\_name format, using standard name resolution rules. Must contain the names of one or more existing tables. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

input\_column\_names (list of lists of str) –

The list of columns from each of the corresponding input tables. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

output\_column\_names (list of str) –

The list of names of the columns to be stored in the output table. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter table\_name. If persist is false (or unspecified), then this is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_table\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the projection as part of input parameter table\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of the schema for the output table. If the schema provided is non-existent, it will be automatically created. The default value is ‘’.

  • mode – The mode describes what rows of the tables being unioned will be retained. Allowed values are:

    • union\_all – Retains all rows from the specified tables.

    • union – Retains all unique rows from the specified tables (synonym for union\_distinct).

    • union\_distinct – Retains all unique rows from the specified tables.

    • except – Retains all unique rows from the first table that do not appear in the second table (only works on 2 tables).

    • except\_all – Retains all rows(including duplicates) from the first table that do not appear in the second table (only works on 2 tables).

    • intersect – Retains all unique rows that appear in both of the specified tables (only works on 2 tables).

    • intersect\_all – Retains all rows(including duplicates) that appear in both of the specified tables (only works on 2 tables).

    The default value is ‘union\_all’.

  • chunk\_size – Indicates the number of records per chunk to be used for this output table.

  • chunk\_column\_max\_memory – Indicates the target maximum data size for each column in a chunk to be used for this output table.

  • chunk\_max\_memory – Indicates the target maximum data size for all columns in a chunk to be used for this output table.

  • create\_indexes – Comma-separated list of columns on which to create indexes on the output table. The columns specified must be present in input parameter output\_column\_names.

  • partition\_type – Partitioning scheme to use for the output table. Allowed values are:

  • partition\_keys – Comma-separated list of partition keys, which are the columns or column expressions by which records will be assigned to partitions defined by partition\_definitions.

  • partition\_definitions – Comma-separated list of partition definitions, whose format depends on the choice of partition\_type. See range partitioning, interval partitioning, list partitioning, hash partitioning, or series partitioning for example formats.

  • is\_automatic\_partition – If true, a new partition will be created for values which don’t fall into an existing partition. Currently only supported for list partitions. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • ttl – Sets the TTL of the output table specified in input parameter table\_name.

  • persist – If true, then the output table specified in input parameter table\_name will be persisted and will not expire unless a ttl is specified. If false, then the output table will be an in-memory table and will expire unless a ttl is specified otherwise. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • view\_id – ID of view of which this output table is a member. The default value is ‘’.

  • force\_replicated – If true, then the output table specified in input parameter table\_name will be replicated even if the source tables are not. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • strategy\_definition – The tier strategy for the table and its columns.

  • compression\_codec – The default compression codec for this table’s columns.

  • no\_count – Return a count of 0 for the union table response to avoid the cost of counting; optimization needed for many chunk virtual unions. The default value is ‘false’.

The default value is an empty dict ( {"{"}{"}"} ).

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

aggregate\_convex\_hull(x\_column\_name=None, y\_column\_name=None, options={"{"}{"}"})

Calculates and returns the convex hull for the values in a table specified by input parameter table\_name.

Parameters

x\_column\_name (str) –

Name of the column containing the x coordinates of the points for the operation being performed.

y\_column\_name (str) –

Name of the column containing the y coordinates of the points for the operation being performed.

options (dict of str to str) –

Optional parameters. The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

x\_vector (list of floats) –

Array of x coordinates of the resulting convex set.

y\_vector (list of floats) –

Array of y coordinates of the resulting convex set.

count (int) –

Count of the number of points in the convex set.

is\_valid (bool) –

Indicates whether a valid convex hull could be computed for the input.

info (dict of str to str) –

Additional information.

Raises

GPUdbException – –

Upon an error from the server.

aggregate\_group\_by(column\_names=None, offset=0, limit=-9999, encoding='binary', options={"{"}{"}"}, force\_primitive\_return\_types=True, get\_column\_major=True)

Calculates unique combinations (groups) of values for the given columns in a given table or view and computes aggregates on each unique combination. This is somewhat analogous to an SQL-style SELECT…GROUP BY.

For aggregation details and examples, see Aggregation. For limitations, see Aggregation Limitations.

Any column(s) can be grouped on, and all column types except unrestricted-length strings may be used for computing applicable aggregates.

The results can be paged via the input parameter offset and input parameter limit parameters. For example, to get 10 groups with the largest counts the inputs would be: limit=10, options={"{"}“sort\_order”:”descending”, “sort\_by”:”value”{"}"}.

Input parameter options can be used to customize behavior of this call e.g. filtering or sorting the results.

To group by columns ‘x’ and ‘y’ and compute the number of objects within each group, use: column\_names=\[‘x’,’y’,’count(\*)’].

To also compute the sum of ‘z’ over each group, use: column\_names=\[‘x’,’y’,’count(\*)’,’sum(z)’].

Available aggregation functions are: count(\*), sum, min, max, avg, mean, stddev, stddev\_pop, stddev\_samp, var, var\_pop, var\_samp, arg\_min, arg\_max and count\_distinct.

Available grouping functions are Rollup, Cube, and Grouping Sets

This service also provides support for Pivot operations.

Filtering on aggregates is supported via expressions using aggregation functions supplied to having.

The response is returned as a dynamic schema. For details see: dynamic schemas documentation.

If a result\_table name is specified in the input parameter options, the results are stored in a new table with that name–no results are returned in the response. Both the table name and resulting column names must adhere to standard naming conventions; column/aggregation expressions will need to be aliased. If the source table’s shard key is used as the grouping column(s) and all result records are selected (input parameter offset is 0 and input parameter limit is -9999), the result table will be sharded, in all other cases it will be replicated. Sorting will properly function only if the result table is replicated or if there is only one processing node and should not be relied upon in other cases. Not available when any of the values of input parameter column\_names is an unrestricted-length string.

Parameters

column\_names (list of str) –

List of one or more column names, expressions, and aggregate expressions. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

offset (long) –

A positive integer indicating the number of initial results to skip (this can be useful for paging through the results). The default value is 0. The minimum allowed value is 0. The maximum allowed value is MAX\_INT.

limit (long) –

A positive integer indicating the maximum number of results to be returned, or END\_OF\_SET (-9999) to indicate that the maximum number of results allowed by the server should be returned. The number of records returned will never exceed the server’s own limit, defined by the max\_get\_records\_size parameter in the server configuration. Use output parameter has\_more\_records to see if more records exist in the result to be fetched, and input parameter offset and input parameter limit to request subsequent pages of results. The default value is -9999.

encoding (str) –

Specifies the encoding for returned records. Allowed values are:

  • binary – Indicates that the returned records should be binary encoded.

  • json – Indicates that the returned records should be JSON-encoded.

The default value is ‘binary’.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of result\_table. If result\_table\_persist is false (or unspecified), then this is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_result\_table\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema as part of result\_table and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema which is to contain the table specified in result\_table. If the schema provided is non-existent, it will be automatically created.

  • expression – Filter expression to apply to the table prior to computing the aggregate group by.

  • pipelined\_expression\_evaluation – Evaluate the group-by during last JoinedSet filter plan step. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • having – Filter expression to apply to the aggregated results.

  • sort\_order – \[DEPRECATED–use order\_by instead] String indicating how the returned values should be sorted - ascending or descending. Allowed values are:

    • ascending – Indicates that the returned values should be sorted in ascending order.

    • descending – Indicates that the returned values should be sorted in descending order.

    The default value is ‘ascending’.

  • sort\_by – \[DEPRECATED–use order\_by instead] String determining how the results are sorted. Allowed values are:

    • key – Indicates that the returned values should be sorted by key, which corresponds to the grouping columns. If you have multiple grouping columns (and are sorting by key), it will first sort the first grouping column, then the second grouping column, etc.

    • value – Indicates that the returned values should be sorted by value, which corresponds to the aggregates. If you have multiple aggregates (and are sorting by value), it will first sort by the first aggregate, then the second aggregate, etc.

    The default value is ‘value’.

  • order\_by – Comma-separated list of the columns to be sorted by as well as the sort direction, e.g., ‘timestamp asc, x desc’. The default value is ‘’.

  • strategy\_definition – The tier strategy for the table and its columns.

  • compression\_codec – The default compression codec for the result table’s columns.

  • result\_table – The name of a table used to store the results, in \[schema\_name.]table\_name format, using standard name resolution rules and meeting table naming criteria. Column names (group-by and aggregate fields) need to be given aliases e.g. \[“FChar256 as fchar256”, “sum(FDouble) as sfd”]. If present, no results are returned in the response. This option is not available if one of the grouping attributes is an unrestricted string (i.e.; not charN) type.

  • result\_table\_persist – If true, then the result table specified in result\_table will be persisted and will not expire unless a ttl is specified. If false, then the result table will be an in-memory table and will expire unless a ttl is specified otherwise. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • result\_table\_force\_replicated – Force the result table to be replicated (ignores any sharding). Must be used in combination with the result\_table option. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • result\_table\_generate\_pk – If true then set a primary key for the result table. Must be used in combination with the result\_table option. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • result\_table\_generate\_soft\_pk – If true then set a soft primary key for the result table. Must be used in combination with the result\_table option. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • ttl – Sets the TTL of the table specified in result\_table.

  • chunk\_size – Indicates the number of records per chunk to be used for the result table. Must be used in combination with the result\_table option.

  • chunk\_column\_max\_memory – Indicates the target maximum data size for each column in a chunk to be used for the result table. Must be used in combination with the result\_table option.

  • chunk\_max\_memory – Indicates the target maximum data size for all columns in a chunk to be used for the result table. Must be used in combination with the result\_table option.

  • create\_indexes – Comma-separated list of columns on which to create indexes on the result table. Must be used in combination with the result\_table option.

  • partition\_type – Partitioning scheme to use for the result table. Allowed values are:

  • partition\_keys – Comma-separated list of partition keys, which are the columns or column expressions by which records will be assigned to partitions defined by partition\_definitions.

  • partition\_definitions – Comma-separated list of partition definitions, whose format depends on the choice of partition\_type. See range partitioning, interval partitioning, list partitioning, hash partitioning, or series partitioning for example formats.

  • is\_automatic\_partition – If true, a new partition will be created for values which don’t fall into an existing partition. Currently only supported for list partitions. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • view\_id – ID of view of which the result table will be a member. The default value is ‘’.

  • pivot – Pivot column.

  • pivot\_values – Comma-separated list of the values in the pivot column. The list provided will become the column header prefixes in the output.

  • grouping\_sets – Customize the grouping attribute sets to compute the aggregates. These sets can include ROLLUP or CUBE operators. The attribute sets should be enclosed in parentheses and can include composite attributes. All attributes specified in the grouping sets must present in the group-by attributes.

  • rollup – This option is used to specify the multilevel aggregates.

  • cube – This option is used to specify the multidimensional aggregates.

  • shard\_key – Comma-separated list of the columns to be sharded on; e.g. ‘column1, column2’. The columns specified must be present in input parameter column\_names. If any alias is given for any column name, the alias must be used, rather than the original column name. The default value is ‘’.

The default value is an empty dict ( {"{"}{"}"} ).

force\_primitive\_return\_types (bool) –

If True, then OrderedDict objects will be returned, where string sub-type columns will have their values converted back to strings; for example, the Python datetime structs, used for datetime type columns would have their values returned as strings. If False, then Record objects will be returned, which for string sub-types, will return native or custom structs; no conversion to string takes place. String conversions, when returning OrderedDicts, incur a speed penalty, and it is strongly recommended to use the Record object option instead. If True, but none of the returned columns require a conversion, then the original Record objects will be returned. Default value is True.

get\_column\_major (bool) –

Indicates if the decoded records will be transposed to be column-major or returned as is (row-major). Default value is True.

Returns

A read-only GPUdbTable object if input options has “result\_table”; –

otherwise the response from the server, which is a dict containing –

the following entries–

response\_schema\_str (str) –

Avro schema of output parameter binary\_encoded\_response or output parameter json\_encoded\_response.

total\_number\_of\_records (long) –

Total/Filtered number of records. This may be an over-estimate if a limit was applied and there are additional records (i.e., when output parameter has\_more\_records is true).

has\_more\_records (bool) –

Too many records. Returned a partial set.

info (dict of str to str) –

Additional information. Allowed keys are:

  • qualified\_result\_table\_name – The fully qualified name of the table (i.e. including the schema) used to store the results.

The default value is an empty dict ( {"{"}{"}"} ).

records (list of Record) –

A list of Record objects which contain the decoded records.

data (list of Record) –

A list of Record objects which contain the decoded records.

Raises

GPUdbException – –

Upon an error from the server.

aggregate\_histogram(column\_name=None, start=None, end=None, interval=None, options={"{"}{"}"})

Performs a histogram calculation given a table, a column, and an interval function. The input parameter interval is used to produce bins of that size and the result, computed over the records falling within each bin, is returned. For each bin, the start value is inclusive, but the end value is exclusive–except for the very last bin for which the end value is also inclusive. The value returned for each bin is the number of records in it, except when a column name is provided as a value\_column. In this latter case the sum of the values corresponding to the value\_column is used as the result instead. The total number of bins requested cannot exceed 10,000.

NOTE: The Kinetica instance being accessed must be running a CUDA (GPU-based) build to service a request that specifies a value\_column.

Parameters

column\_name (str) –

Name of a column or an expression of one or more column names over which the histogram will be calculated.

start (float) –

Lower end value of the histogram interval, inclusive.

end (float) –

Upper end value of the histogram interval, inclusive.

interval (float) –

The size of each bin within the start and end parameters.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • value\_column – The name of the column to use when calculating the bin values (values are summed). The column must be a numerical type (int, double, long, float).

  • start – The start parameter for char types.

  • end – The end parameter for char types.

  • interval – The interval parameter for char types.

The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

counts (list of floats) –

The array of calculated values that represents the histogram data points.

start (float) –

Value of input parameter start.

end (float) –

Value of input parameter end.

info (dict of str to str) –

Additional information.

Raises

GPUdbException – –

Upon an error from the server.

aggregate\_k\_means(column\_names=None, k=None, tolerance=None, options={"{"}{"}"})

This endpoint runs the k-means algorithm - a heuristic algorithm that attempts to do k-means clustering. An ideal k-means clustering algorithm selects k points such that the sum of the mean squared distances of each member of the set to the nearest of the k points is minimized. The k-means algorithm however does not necessarily produce such an ideal cluster. It begins with a randomly selected set of k points and then refines the location of the points iteratively and settles to a local minimum. Various parameters and options are provided to control the heuristic search.

NOTE: The Kinetica instance being accessed must be running a CUDA (GPU-based) build to service this request.

Parameters

column\_names (list of str) –

List of column names on which the operation would be performed. If n columns are provided then each of the k result points will have n dimensions corresponding to the n columns. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

k (int) –

The number of mean points to be determined by the algorithm.

tolerance (float) –

Stop iterating when the distances between successive points is less than the given tolerance.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • whiten – When set to 1 each of the columns is first normalized by its stdv - default is not to whiten.

  • max\_iters – Number of times to try to hit the tolerance limit before giving up - default is 10.

  • num\_tries – Number of times to run the k-means algorithm with a different randomly selected starting points - helps avoid local minimum. Default is 1.

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of result\_table. If result\_table\_persist is false (or unspecified), then this is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_result\_table\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • result\_table – The name of a table used to store the results, in \[schema\_name.]table\_name format, using standard name resolution rules and meeting table naming criteria. If this option is specified, the results are not returned in the response.

  • result\_table\_persist – If true, then the result table specified in result\_table will be persisted and will not expire unless a ttl is specified. If false, then the result table will be an in-memory table and will expire unless a ttl is specified otherwise. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • ttl – Sets the TTL of the table specified in result\_table.

The default value is an empty dict ( {"{"}{"}"} ).

Returns

A read-only GPUdbTable object if input options has “result\_table”; –

otherwise the response from the server, which is a dict containing –

the following entries–

means (list of lists of floats) –

The k-mean values found.

counts (list of longs) –

The number of elements in the cluster closest the corresponding k-means values.

rms\_dists (list of floats) –

The root mean squared distance of the elements in the cluster for each of the k-means values.

count (long) –

The total count of all the clusters - will be the size of the input table.

rms\_dist (float) –

The sum of all the rms\_dists - the value the k-means algorithm is attempting to minimize.

tolerance (float) –

The distance between the last two iterations of the algorithm before it quit.

num\_iters (int) –

The number of iterations the algorithm executed before it quit.

info (dict of str to str) –

Additional information. Allowed keys are:

  • qualified\_result\_table\_name – The fully qualified name of the result table (i.e. including the schema) used to store the results.

The default value is an empty dict ( {"{"}{"}"} ).

Raises

GPUdbException – –

Upon an error from the server.

aggregate\_min\_max(column\_name=None, options={"{"}{"}"})

Calculates and returns the minimum and maximum values of a particular column in a table.

Parameters

column\_name (str) –

Name of a column or an expression of one or more column on which the min-max will be calculated.

options (dict of str to str) –

Optional parameters. The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

min (float) –

Minimum value of the input parameter column\_name.

max (float) –

Maximum value of the input parameter column\_name.

info (dict of str to str) –

Additional information. Allowed keys are:

  • min\_string – The minimum value of input parameter column\_name, stored as a byte vector.

  • max\_string – The maximum value of input parameter column\_name, stored as a byte vector.

The default value is an empty dict ( {"{"}{"}"} ).

Raises

GPUdbException – –

Upon an error from the server.

aggregate\_min\_max\_geometry(column\_name=None, options={"{"}{"}"})

Calculates and returns the minimum and maximum x- and y-coordinates of a particular geospatial geometry column in a table.

Parameters

column\_name (str) –

Name of a geospatial geometry column on which the min-max will be calculated.

options (dict of str to str) –

Optional parameters. The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

min\_x (float) –

Minimum x-coordinate value of the input parameter column\_name.

max\_x (float) –

Maximum x-coordinate value of the input parameter column\_name.

min\_y (float) –

Minimum y-coordinate value of the input parameter column\_name.

max\_y (float) –

Maximum y-coordinate value of the input parameter column\_name.

info (dict of str to str) –

Additional information.

Raises

GPUdbException – –

Upon an error from the server.

aggregate\_statistics(column\_name=None, stats=None, options={"{"}{"}"})

Calculates the requested statistics of the given column(s) in a given table.

The available statistics are: count (number of total objects), mean, stdv (standard deviation), variance, skew, kurtosis, sum, min, max, weighted\_average, cardinality (unique count), estimated\_cardinality, percentile, and percentile\_rank.

Estimated cardinality is calculated by using the hyperloglog approximation technique.

Percentiles and percentile ranks are approximate and are calculated using the t-digest algorithm. They must include the desired percentile/percentile\_rank. To compute multiple percentiles each value must be specified separately (i.e. ‘percentile(75.0),percentile(99.0),percentile\_rank(1234.56),percentile\_rank(-5)’).

A second, comma-separated value can be added to the percentile statistic to calculate percentile resolution, e.g., a 50th percentile with 200 resolution would be ‘percentile(50,200)’.

The weighted average statistic requires a weight column to be specified in weight\_column\_name. The weighted average is then defined as the sum of the products of input parameter column\_name times the weight\_column\_name values divided by the sum of the weight\_column\_name values.

Additional columns can be used in the calculation of statistics via additional\_column\_names. Values in these columns will be included in the overall aggregate calculation–individual aggregates will not be calculated per additional column. For instance, requesting the count and mean of input parameter column\_name x and additional\_column\_names y and z, where x holds the numbers 1-10, y holds 11-20, and z holds 21-30, would return the total number of x, y, and z values (30), and the single average value across all x, y, and z values (15.5).

The response includes a list of key/value pairs of each statistic requested and its corresponding value.

Parameters

column\_name (str) –

Name of the primary column for which the statistics are to be calculated.

stats (str) –

Comma separated list of the statistics to calculate, e.g. “sum,mean”. Allowed values are:

  • count – Number of objects (independent of the given column(s)).

  • mean – Arithmetic mean (average), equivalent to sum/count.

  • stdv – Sample standard deviation (denominator is count-1).

  • variance – Unbiased sample variance (denominator is count-1).

  • skew – Skewness (third standardized moment).

  • kurtosis – Kurtosis (fourth standardized moment).

  • sum – Sum of all values in the column(s).

  • min – Minimum value of the column(s).

  • max – Maximum value of the column(s).

  • weighted\_average – Weighted arithmetic mean (using the option weight\_column\_name as the weighting column).

  • cardinality – Number of unique values in the column(s).

  • estimated\_cardinality – Estimate (via hyperloglog technique) of the number of unique values in the column(s).

  • percentile – Estimate (via t-digest) of the given percentile of the column(s) (percentile(50.0) will be an approximation of the median). Add a second, comma-separated value to calculate percentile resolution, e.g., ‘percentile(75,150)’.

  • percentile\_rank – Estimate (via t-digest) of the percentile rank of the given value in the column(s) (if the given value is the median of the column(s), percentile\_rank(\) will return approximately 50.0).

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • additional\_column\_names – A list of comma separated column names over which statistics can be accumulated along with the primary column. All columns listed and input parameter column\_name must be of the same type. Must not include the column specified in input parameter column\_name and no column can be listed twice.

  • weight\_column\_name – Name of column used as weighting attribute for the weighted average statistic.

The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

stats (dict of str to floats) –

(statistic name, double value) pairs of the requested statistics, including the total count by default.

info (dict of str to str) –

Additional information.

Raises

GPUdbException – –

Upon an error from the server.

aggregate\_statistics\_by\_range(select\_expression='', column\_name=None, value\_column\_name=None, stats=None, start=None, end=None, interval=None, options={"{"}{"}"})

Divides the given set into bins and calculates statistics of the values of a value-column in each bin. The bins are based on the values of a given binning-column. The statistics that may be requested are mean, stdv (standard deviation), variance, skew, kurtosis, sum, min, max, first, last and weighted average. In addition to the requested statistics the count of total samples in each bin is returned. This counts vector is just the histogram of the column used to divide the set members into bins. The weighted average statistic requires a weight column to be specified in weight\_column\_name. The weighted average is then defined as the sum of the products of the value column times the weight column divided by the sum of the weight column.

There are two methods for binning the set members. In the first, which can be used for numeric valued binning-columns, a min, max and interval are specified. The number of bins, nbins, is the integer upper bound of (max-min)/interval. Values that fall in the range \[min+n\*interval,min+(n+1)\*interval) are placed in the nth bin where n ranges from 0..nbin-2. The final bin is \[min+(nbin-1)\*interval,max]. In the second method, bin\_values specifies a list of binning column values. Binning-columns whose value matches the nth member of the bin\_values list are placed in the nth bin. When a list is provided, the binning-column must be of type string or int.

NOTE: The Kinetica instance being accessed must be running a CUDA (GPU-based) build to service this request.

Parameters

select\_expression (str) –

For a non-empty expression statistics are calculated for those records for which the expression is true. The default value is ‘’.

column\_name (str) –

Name of the binning-column used to divide the set samples into bins.

value\_column\_name (str) –

Name of the value-column for which statistics are to be computed.

stats (str) –

A string of comma separated list of the statistics to calculate, e.g. ‘sum,mean’. Available statistics: mean, stdv (standard deviation), variance, skew, kurtosis, sum.

start (float) –

The lower bound of the binning-column.

end (float) –

The upper bound of the binning-column.

interval (float) –

The interval of a bin. Set members fall into bin i if the binning-column falls in the range \[start+interval\*i, start+interval\*(i+1)).

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • additional\_column\_names – A list of comma separated value-column names over which statistics can be accumulated along with the primary value\_column.

  • bin\_values – A list of comma separated binning-column values. Values that match the nth bin\_values value are placed in the nth bin.

  • weight\_column\_name – Name of the column used as weighting column for the weighted\_average statistic.

  • order\_column\_name – Name of the column used for candlestick charting techniques.

The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

stats (dict of str to lists of floats) –

A map with a key for each statistic in the stats input parameter having a value that is a vector of the corresponding value-column bin statistics. In a addition the key count has a value that is a histogram of the binning-column.

info (dict of str to str) –

Additional information.

Raises

GPUdbException – –

Upon an error from the server.

aggregate\_unique(column\_name=None, offset=0, limit=-9999, encoding='binary', options={"{"}{"}"}, force\_primitive\_return\_types=True, get\_column\_major=True)

Returns all the unique values from a particular column (specified by input parameter column\_name) of a particular table or view (specified by input parameter table\_name). If input parameter column\_name is a numeric column, the values will be in output parameter binary\_encoded\_response. Otherwise if input parameter column\_name is a string column, the values will be in output parameter json\_encoded\_response. The results can be paged via input parameter offset and input parameter limit parameters.

{"{"}“limit”:”10”,”sort\_order”:”descending”{"}"}

The response is returned as a dynamic schema. For details see: dynamic schemas documentation.

If a result\_table name is specified in the input parameter options, the results are stored in a new table with that name–no results are returned in the response. Both the table name and resulting column name must adhere to standard naming conventions; any column expression will need to be aliased. If the source table’s shard key is used as the input parameter column\_name, the result table will be sharded, in all other cases it will be replicated. Sorting will properly function only if the result table is replicated or if there is only one processing node and should not be relied upon in other cases. Not available if the value of input parameter column\_name is an unrestricted-length string.

Parameters

column\_name (str) –

Name of the column or an expression containing one or more column names on which the unique function would be applied.

offset (long) –

A positive integer indicating the number of initial results to skip (this can be useful for paging through the results). The default value is 0. The minimum allowed value is 0. The maximum allowed value is MAX\_INT.

limit (long) –

A positive integer indicating the maximum number of results to be returned, or END\_OF\_SET (-9999) to indicate that the maximum number of results allowed by the server should be returned. The number of records returned will never exceed the server’s own limit, defined by the max\_get\_records\_size parameter in the server configuration. Use output parameter has\_more\_records to see if more records exist in the result to be fetched, and input parameter offset and input parameter limit to request subsequent pages of results. The default value is -9999.

encoding (str) –

Specifies the encoding for returned records. Allowed values are:

  • binary – Indicates that the returned records should be binary encoded.

  • json – Indicates that the returned records should be JSON-encoded.

The default value is ‘binary’.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of result\_table. If result\_table\_persist is false (or unspecified), then this is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_result\_table\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema as part of result\_table and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema which is to contain the table specified in result\_table. If the schema provided is non-existent, it will be automatically created.

  • expression – Filter expression to apply to the table.

  • sort\_order – String indicating how the returned values should be sorted. Allowed values are:

    • ascending

    • descending

    The default value is ‘ascending’.

  • order\_by – Comma-separated list of the columns to be sorted by as well as the sort direction, e.g., ‘timestamp asc, x desc’. The default value is ‘’.

  • result\_table – The name of the table used to store the results, in \[schema\_name.]table\_name format, using standard name resolution rules and meeting table naming criteria. If present, no results are returned in the response. Not available if input parameter column\_name is an unrestricted-length string.

  • result\_table\_persist – If true, then the result table specified in result\_table will be persisted and will not expire unless a ttl is specified. If false, then the result table will be an in-memory table and will expire unless a ttl is specified otherwise. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • result\_table\_force\_replicated – Force the result table to be replicated (ignores any sharding). Must be used in combination with the result\_table option. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • result\_table\_generate\_pk – If true then set a primary key for the result table. Must be used in combination with the result\_table option. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • ttl – Sets the TTL of the table specified in result\_table.

  • chunk\_size – Indicates the number of records per chunk to be used for the result table. Must be used in combination with the result\_table option.

  • chunk\_column\_max\_memory – Indicates the target maximum data size for each column in a chunk to be used for the result table. Must be used in combination with the result\_table option.

  • chunk\_max\_memory – Indicates the target maximum data size for all columns in a chunk to be used for the result table. Must be used in combination with the result\_table option.

  • compression\_codec – The default compression codec for the result table’s columns.

  • view\_id – ID of view of which the result table will be a member. The default value is ‘’.

The default value is an empty dict ( {"{"}{"}"} ).

force\_primitive\_return\_types (bool) –

If True, then OrderedDict objects will be returned, where string sub-type columns will have their values converted back to strings; for example, the Python datetime structs, used for datetime type columns would have their values returned as strings. If False, then Record objects will be returned, which for string sub-types, will return native or custom structs; no conversion to string takes place. String conversions, when returning OrderedDicts, incur a speed penalty, and it is strongly recommended to use the Record object option instead. If True, but none of the returned columns require a conversion, then the original Record objects will be returned. Default value is True.

get\_column\_major (bool) –

Indicates if the decoded records will be transposed to be column-major or returned as is (row-major). Default value is True.

Returns

A read-only GPUdbTable object if input options has “result\_table”; –

otherwise the response from the server, which is a dict containing –

the following entries–

table\_name (str) –

The same table name as was passed in the parameter list.

response\_schema\_str (str) –

Avro schema of output parameter binary\_encoded\_response or output parameter json\_encoded\_response.

has\_more\_records (bool) –

Too many records. Returned a partial set.

info (dict of str to str) –

Additional information. Allowed keys are:

  • qualified\_result\_table\_name – The fully qualified name of the table (i.e. including the schema) used to store the results.

The default value is an empty dict ( {"{"}{"}"} ).

records (list of Record) –

A list of Record objects which contain the decoded records.

data (list of Record) –

A list of Record objects which contain the decoded records.

Raises

GPUdbException – –

Upon an error from the server.

aggregate\_unpivot(column\_names=None, variable\_column\_name='', value\_column\_name='', pivoted\_columns=None, encoding='binary', options={"{"}{"}"}, force\_primitive\_return\_types=True, get\_column\_major=True)

Rotate the column values into rows values.

For unpivot details and examples, see Unpivot. For limitations, see Unpivot Limitations.

Unpivot is used to normalize tables that are built for cross tabular reporting purposes. The unpivot operator rotates the column values for all the pivoted columns. A variable column, value column and all columns from the source table except the unpivot columns are projected into the result table. The variable column and value columns in the result table indicate the pivoted column name and values respectively.

The response is returned as a dynamic schema. For details see: dynamic schemas documentation.

Parameters

column\_names (list of str) –

List of column names or expressions. A wildcard ‘\*’ can be used to include all the non-pivoted columns from the source table. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

variable\_column\_name (str) –

Specifies the variable/parameter column name. The default value is ‘’.

value\_column\_name (str) –

Specifies the value column name. The default value is ‘’.

pivoted\_columns (list of str) –

List of one or more values typically the column names of the input table. All the columns in the source table must have the same data type. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

encoding (str) –

Specifies the encoding for returned records. Allowed values are:

  • binary – Indicates that the returned records should be binary encoded.

  • json – Indicates that the returned records should be JSON-encoded.

The default value is ‘binary’.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of result\_table. If result\_table\_persist is false (or unspecified), then this is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_result\_table\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema as part of result\_table and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema which is to contain the table specified in result\_table. If the schema is non-existent, it will be automatically created.

  • result\_table – The name of a table used to store the results, in \[schema\_name.]table\_name format, using standard name resolution rules and meeting table naming criteria. If present, no results are returned in the response.

  • result\_table\_persist – If true, then the result table specified in result\_table will be persisted and will not expire unless a ttl is specified. If false, then the result table will be an in-memory table and will expire unless a ttl is specified otherwise. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • expression – Filter expression to apply to the table prior to unpivot processing.

  • order\_by – Comma-separated list of the columns to be sorted by; e.g. ‘timestamp asc, x desc’. The columns specified must be present in input table. If any alias is given for any column name, the alias must be used, rather than the original column name. The default value is ‘’.

  • chunk\_size – Indicates the number of records per chunk to be used for the result table. Must be used in combination with the result\_table option.

  • chunk\_column\_max\_memory – Indicates the target maximum data size for each column in a chunk to be used for the result table. Must be used in combination with the result\_table option.

  • chunk\_max\_memory – Indicates the target maximum data size for all columns in a chunk to be used for the result table. Must be used in combination with the result\_table option.

  • compression\_codec – The default compression codec for the result table’s columns.

  • limit – The number of records to keep. The default value is ‘’.

  • ttl – Sets the TTL of the table specified in result\_table.

  • view\_id – View this result table is part of. The default value is ‘’.

  • create\_indexes – Comma-separated list of columns on which to create indexes on the table specified in result\_table. The columns specified must be present in output column names. If any alias is given for any column name, the alias must be used, rather than the original column name.

  • result\_table\_force\_replicated – Force the result table to be replicated (ignores any sharding). Must be used in combination with the result\_table option. Allowed values are:

    • true

    • false

    The default value is ‘false’.

The default value is an empty dict ( {"{"}{"}"} ).

force\_primitive\_return\_types (bool) –

If True, then OrderedDict objects will be returned, where string sub-type columns will have their values converted back to strings; for example, the Python datetime structs, used for datetime type columns would have their values returned as strings. If False, then Record objects will be returned, which for string sub-types, will return native or custom structs; no conversion to string takes place. String conversions, when returning OrderedDicts, incur a speed penalty, and it is strongly recommended to use the Record object option instead. If True, but none of the returned columns require a conversion, then the original Record objects will be returned. Default value is True.

get\_column\_major (bool) –

Indicates if the decoded records will be transposed to be column-major or returned as is (row-major). Default value is True.

Returns

A read-only GPUdbTable object if input options has “result\_table”; –

otherwise the response from the server, which is a dict containing –

the following entries–

table\_name (str) –

Typically shows the result-table name if provided in the request (Ignore otherwise).

response\_schema\_str (str) –

Avro schema of output parameter binary\_encoded\_response or output parameter json\_encoded\_response.

total\_number\_of\_records (long) –

Total/Filtered number of records.

has\_more\_records (bool) –

Too many records. Returned a partial set.

info (dict of str to str) –

Additional information. Allowed keys are:

  • qualified\_result\_table\_name – The fully qualified name of the table (i.e. including the schema) used to store the results.

The default value is an empty dict ( {"{"}{"}"} ).

records (list of Record) –

A list of Record objects which contain the decoded records.

data (list of Record) –

A list of Record objects which contain the decoded records.

Raises

GPUdbException – –

Upon an error from the server.

alter\_table(action=None, value=None, options={"{"}{"}"})

Apply various modifications to a table or view. The available modifications include the following:

Manage a table’s columns–a column can be added, removed, or have its type and properties modified, including whether it is dictionary encoded or not.

External tables cannot be modified except for their refresh method.

Create or delete a column, low-cardinality index, chunk skip, geospatial, CAGRA, or HNSW index. This can speed up certain operations when using expressions containing equality or relational operators on indexed columns. This only applies to tables.

Create or delete a foreign key on a particular column.

Manage a range-partitioned or a manual list-partitioned table’s partitions.

Set (or reset) the tier strategy of a table or view.

Refresh and manage the refresh mode of a materialized view or an external table.

Set the time-to-live (TTL). This can be applied to tables or views.

Set the global access mode (i.e. locking) for a table. This setting trumps any role-based access controls that may be in place; e.g., a user with write access to a table marked read-only will not be able to insert records into it. The mode can be set to read-only, write-only, read/write, and no access.

Parameters

action (str) –

Modification operation to be applied. Allowed values are:

  • create\_index – Creates a column (attribute) index, low-cardinality index, chunk skip index, geospatial index, CAGRA index, or HNSW index (depending on the specified index\_type), on the column name specified in input parameter value. If this column already has the specified index, an error will be returned.

  • refresh\_index – Refreshes an index identified by index\_type, on the column name specified in input parameter value. Currently applicable only to CAGRA indices.

  • delete\_index – Deletes a column (attribute) index, low-cardinality index, chunk skip index, geospatial index, CAGRA index, or HNSW index (depending on the specified index\_type), on the column name specified in input parameter value. If this column does not have the specified index, an error will be returned.

  • move\_to\_collection – \[DEPRECATED–please use move\_to\_schema and use GPUdb.create\_schema() to create the schema if non-existent] Moves a table or view into a schema named input parameter value. If the schema provided is non-existent, it will be automatically created.

  • move\_to\_schema – Moves a table or view into a schema named input parameter value. If the schema provided is nonexistent, an error will be thrown. If input parameter value is empty, then the table or view will be placed in the user’s default schema.

  • protected – No longer used. Previously set whether the given input parameter table\_name should be protected or not. The input parameter value would have been either ‘true’ or ‘false’.

  • rename\_table – Renames a table or view to input parameter value. Has the same naming restrictions as tables.

  • ttl – Sets the time-to-live in minutes of the table or view specified in input parameter table\_name.

  • add\_comment – Adds the comment specified in input parameter value to the table specified in input parameter table\_name. Use column\_name to set the comment for a column.

  • add\_column – Adds the column specified in input parameter value to the table specified in input parameter table\_name. Use column\_type and column\_properties in input parameter options to set the column’s type and properties, respectively.

  • change\_column – Changes type and properties of the column specified in input parameter value. Use column\_type and column\_properties in input parameter options to set the column’s type and properties, respectively. Note that primary key and/or shard key columns cannot be changed. All unchanging column properties must be listed for the change to take place, e.g., to add dictionary encoding to an existing ‘char4’ column, both ‘char4’ and ‘dict’ must be specified in the input parameter options map.

  • delete\_column – Deletes the column specified in input parameter value from the table specified in input parameter table\_name.

  • set\_default – Sets or replaces the default value expression for the column specified in input parameter value. The new default is taken from add\_column\_expression. Existing properties on the column are preserved.

  • delete\_default – Removes the default value expression from the column specified in input parameter value. Other column properties are preserved.

  • create\_foreign\_key – Creates a foreign key specified in input parameter value using the format ‘(source\_column\_name \[, …]) references target\_table\_name(primary\_key\_column\_name \[, …]) \[as foreign\_key\_name]’.

  • delete\_foreign\_key – Deletes a foreign key. The input parameter value should be the foreign\_key\_name specified when creating the key or the complete string used to define it.

  • add\_partition – Adds the partition specified in input parameter value, to either a range-partitioned or manual list-partitioned table.

  • remove\_partition – Removes the partition specified in input parameter value (and relocates all of its data to the default partition) from either a range-partitioned or manual list-partitioned table.

  • delete\_partition – Deletes the partition specified in input parameter value (and all of its data) from either a range-partitioned or manual list-partitioned table.

  • set\_global\_access\_mode – Sets the global access mode (i.e. locking) for the table specified in input parameter table\_name. Specify the access mode in input parameter value. Valid modes are ‘no\_access’, ‘read\_only’, ‘write\_only’ and ‘read\_write’.

  • refresh – For a materialized view, replays all the table creation commands required to create the view. For an external table, reloads all data in the table from its associated source files or data source.

  • set\_refresh\_method – For a materialized view, sets the method by which the view is refreshed to the method specified in input parameter value - one of ‘manual’, ‘periodic’, or ‘on\_change’. For an external table, sets the method by which the table is refreshed to the method specified in input parameter value - either ‘manual’ or ‘on\_start’.

  • set\_refresh\_start\_time – Sets the time to start periodic refreshes of this materialized view to the datetime string specified in input parameter value with format ‘YYYY-MM-DD HH:MM:SS’. Subsequent refreshes occur at the specified time + N \* the refresh period.

  • set\_refresh\_stop\_time – Sets the time to stop periodic refreshes of this materialized view to the datetime string specified in input parameter value with format ‘YYYY-MM-DD HH:MM:SS’.

  • set\_refresh\_period – Sets the time interval in seconds at which to refresh this materialized view to the value specified in input parameter value. Also, sets the refresh method to periodic if not already set.

  • set\_refresh\_span – Sets the future time-offset(in seconds) for the view refresh to stop.

  • set\_refresh\_execute\_as – Sets the user name to refresh this materialized view to the value specified in input parameter value.

  • remove\_text\_search\_attributes – Removes text search attribute from all columns.

  • remove\_shard\_keys – Removes the shard key property from all columns, so that the table will be considered randomly sharded. The data is not moved. The input parameter value is ignored.

  • set\_strategy\_definition – Sets the tier strategy for the table and its columns to the one specified in input parameter value, replacing the existing tier strategy in its entirety.

  • cancel\_datasource\_subscription – Permanently unsubscribe a data source that is loading continuously as a stream. The data source can be Kafka / S3 / Azure / GCS.

  • drop\_datasource\_subscription – Permanently delete a cancelled data source subscription.

  • pause\_datasource\_subscription – Temporarily unsubscribe a data source that is loading continuously as a stream. The data source can be Kafka / S3 / Azure / GCS.

  • resume\_datasource\_subscription – Resubscribe to a paused data source subscription. The data source can be Kafka / S3 / Azure / GCS.

  • change\_owner – Change the owner resource group of the table.

  • set\_load\_vectors\_policy – Set startup data loading scheme for the table; see description of ‘load\_vectors\_policy’ in GPUdb.create\_table() for possible values for input parameter value.

  • set\_build\_pk\_index\_policy – Set startup primary key generation scheme for the table; see description of ‘build\_pk\_index\_policy’ in GPUdb.create\_table() for possible values for input parameter value.

  • set\_build\_materialized\_view\_policy – Set startup rebuilding scheme for the materialized view; see description of ‘build\_materialized\_view\_policy’ in GPUdb.create\_materialized\_view() for possible values for input parameter value.

value (str) –

The value of the modification, depending on input parameter action. For example, if input parameter action is add\_column, this would be the column name; while the column’s definition would be covered by the column\_type, column\_properties, column\_default\_value, and add\_column\_expression in input parameter options. If input parameter action is ttl, it would be the number of minutes for the new TTL. If input parameter action is refresh, this field would be blank.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • action

  • column\_name

  • table\_name

  • column\_default\_value – When adding a column, set a literal default value for existing records. For nullable columns, the default value will be null, regardless of data type. Also persisted as the column’s default for future inserts that omit the column.

  • column\_properties – When adding or changing a column, set the column properties (strings, separated by a comma: data, text\_search, char8, int8 etc).

  • column\_type – When adding or changing a column, set the column type (strings, separated by a comma: int, double, string, null etc).

  • copy\_values\_from\_column – \[DEPRECATED] Please use add\_column\_expression instead.

  • rename\_column – When changing a column, specify new column name.

  • validate\_change\_column – When changing a column, validate the change before applying it (or not). Allowed values are:

    • true – Validate all values. A value too large (or too long) for the new type will prevent any change.

    • false – When a value is too large or long, it will be truncated.

    The default value is ‘true’.

  • update\_last\_access\_time – Indicates whether the time-to-live (TTL) expiration countdown timer should be reset to the table’s TTL. Allowed values are:

    • true – Reset the expiration countdown timer to the table’s configured TTL.

    • false – Don’t reset the timer; expiration countdown will continue from where it is, as if the table had not been accessed.

    The default value is ‘true’.

  • add\_column\_expression – When adding a column or setting a new default with input parameter action set to set\_default, the new default expression (GPUdb-syntax) for the column. Any valid expression may be used, including one containing references to existing columns in the same table. Persisted as the column’s default for future inserts that omit the column; for add\_column, also used to backfill existing rows.

  • add\_column\_expression\_sql – Optional SQL-syntax form of add\_column\_expression, used only when the SQL syntax differs from the GPUdb syntax. Persisted alongside the GPUdb form so SHOW CREATE TABLE / information\_schema can reproduce the original SQL.

  • strategy\_definition – Parameter for specifying the tier strategy for the table and its columns when input parameter action is set\_strategy\_definition, replacing the existing tier strategy in its entirety.

  • index\_type – Type of index to create, when input parameter action is create\_index; to refresh, when input parameter action is refresh\_index; or to delete, when input parameter action is delete\_index. Allowed values are:

    The default value is ‘column’.

  • index\_options – Options to use when creating an index, in the format “key: value \[, key: value \[, …]]”. Valid options vary by index type.

The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

table\_name (str) –

Table on which the operation was performed.

action (str) –

Modification operation that was performed.

value (str) –

The value of the modification that was performed.

type\_id (str) –

Return the type\_id (when changing a table, a new type may be created).

type\_definition (str) –

Return the type\_definition (when changing a table, a new type may be created).

properties (dict of str to lists of str) –

Return the type properties (when changing a table, a new type may be created).

label (str) –

Return the type label (when changing a table, a new type may be created).

info (dict of str to str) –

Additional information.

Raises

GPUdbException – –

Upon an error from the server.

alter\_table\_columns(column\_alterations=None, options=None)

Apply various modifications to columns in a table, view. The available modifications include the following:

Create or delete an index on a particular column. This can speed up certain operations when using expressions containing equality or relational operators on indexed columns. This only applies to tables.

Manage a table’s columns–a column can be added, removed, or have its type and properties modified, including whether it is dictionary encoded or not.

Parameters

column\_alterations (list of dicts of str to str) –

List of alter table add/delete/change column requests - all for the same table. Each request is a map that includes ‘column\_name’, ‘action’ and the options specific for the action. Note that the same options as in alter table requests but in the same map as the column name and the action. For example: \[{"{"}‘column\_name’:’col\_1’,’action’:’change\_column’,’rename\_column’:’col\_2’{"}"},{"{"}‘column\_name’:’col\_1’,’action’:’add\_column’, ‘type’:’int’,’default\_value’:’1’{"}"}]. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

options (dict of str to str) –

Optional parameters.

Returns

The response from the server which is a dict containing the –

following entries–

table\_name (str) –

Table on which the operation was performed.

type\_id (str) –

Return the type\_id (when changing a table, a new type may be created).

type\_definition (str) –

Return the type\_definition (when changing a table, a new type may be created).

properties (dict of str to lists of str) –

Return the type properties (when changing a table, a new type may be created).

label (str) –

Return the type label (when changing a table, a new type may be created).

column\_alterations (list of dicts of str to str) –

List of alter table add/delete/change column requests - all for the same table. Each request is a map that includes ‘column\_name’, ‘action’ and the options specific for the action. Note that the same options as in alter table requests but in the same map as the column name and the action. For example: \[{"{"}‘column\_name’:’col\_1’,’action’:’change\_column’,’rename\_column’:’col\_2’{"}"},{"{"}‘column\_name’:’col\_1’,’action’:’add\_column’, ‘type’:’int’,’default\_value’:’1’{"}"}].

info (dict of str to str) –

Additional information.

Raises

GPUdbException – –

Upon an error from the server.

append\_records(source\_table\_name=None, field\_map=None, options={"{"}{"}"})

Append (or insert) all records from a source table (specified by input parameter source\_table\_name) to a particular target table (specified by input parameter table\_name). The field map (specified by input parameter field\_map) holds the user specified map of target table column names with their mapped source column names.

Parameters

source\_table\_name (str) –

The source table name to get records from, in \[schema\_name.]table\_name format, using standard name resolution rules. Must be an existing table name.

field\_map (dict of str to str) –

Contains the mapping of column names from the target table (specified by input parameter table\_name) as the keys, and corresponding column names or expressions (e.g., ‘col\_name+1’) from the source table (specified by input parameter source\_table\_name). Must be existing column names in source table and target table, and their types must be matched. For details on using expressions, see Expressions.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • offset – A positive integer indicating the number of initial results to skip from input parameter source\_table\_name. Default is 0. The minimum allowed value is 0. The maximum allowed value is MAX\_INT. The default value is ‘0’.

  • limit – A positive integer indicating the maximum number of results to be returned from input parameter source\_table\_name. Or END\_OF\_SET (-9999) to indicate that the max number of results should be returned. The default value is ‘-9999’.

  • expression – Filter expression to apply to the input parameter source\_table\_name. The default value is ‘’.

  • order\_by – Comma-separated list of the columns to be sorted by from source table (specified by input parameter source\_table\_name), e.g., ‘timestamp asc, x desc’. The order\_by columns do not have to be present in input parameter field\_map. The default value is ‘’.

  • update\_on\_existing\_pk – Specifies the record collision policy for inserting source table records (specified by input parameter source\_table\_name) into a target table (specified by input parameter table\_name) with a primary key. If set to true, any existing table record with primary key values that match those of a source table record being inserted will be replaced by that new record (the new data will be “upserted”). If set to false, any existing table record with primary key values that match those of a source table record being inserted will remain unchanged, while the source record will be rejected and an error handled as determined by ignore\_existing\_pk. If the specified table does not have a primary key, then this option has no effect. Allowed values are:

    • true – Upsert new records when primary keys match existing records.

    • false – Reject new records when primary keys match existing records.

    The default value is ‘false’.

  • enable\_inplace\_updates – Applies only when upserting (when update\_on\_existing\_pk is true). If set to true, an existing record matched by primary key is modified in place. If set to false, it is updated by deleting the existing record and inserting a replacement (delete and insert), which prevents the change from being reflected in dependent materialized views until they are refreshed. Allowed values are:

    • true

    • false

    The default value is ‘true’.

  • ignore\_existing\_pk – Specifies the record collision error-suppression policy for inserting source table records (specified by input parameter source\_table\_name) into a target table (specified by input parameter table\_name) with a primary key, only used when not in upsert mode (upsert mode is disabled when update\_on\_existing\_pk is false). If set to true, any source table record being inserted that is rejected for having primary key values that match those of an existing target table record will be ignored with no error generated. If false, the rejection of any source table record for having primary key values matching an existing target table record will result in an error being raised. If the specified table does not have a primary key or if upsert mode is in effect (update\_on\_existing\_pk is true), then this option has no effect. Allowed values are:

    • true – Ignore source table records whose primary key values collide with those of target table records.

    • false – Raise an error for any source table record whose primary key values collide with those of a target table record.

    The default value is ‘false’.

  • pk\_conflict\_predicate\_higher – The record with higher value for the column resolves the primary-key insert conflict. The default value is ‘’.

  • pk\_conflict\_predicate\_lower – The record with lower value for the column resolves the primary-key insert conflict. The default value is ‘’.

  • truncate\_strings – If set to true, it allows inserting longer strings into smaller charN string columns by truncating the longer strings to fit. Allowed values are:

    • true

    • false

    The default value is ‘false’.

The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

table\_name (str) –

The name of the table to which the records were appended.

info (dict of str to str) –

Additional information. The default value is an empty dict ( {"{"}{"}"} ).

Raises

GPUdbException – –

Upon an error from the server.

clear\_statistics(column\_name='', options={"{"}{"}"})

Clears statistics (cardinality, mean value, etc.) for a column in a specified table.

Parameters

column\_name (str) –

Name of the column in input parameter table\_name for which to clear statistics. The column must be from an existing table. An empty string clears statistics for all columns in the table. The default value is ‘’.

options (dict of str to str) –

Optional parameters. The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

table\_name (str) –

Value of input parameter table\_name.

column\_name (str) –

Value of input parameter column\_name.

info (dict of str to str) –

Additional information.

Raises

GPUdbException – –

Upon an error from the server.

clear(authorization='', options={"{"}{"}"})

Clears (drops) one or all tables in the database cluster. The operation is synchronous meaning that the table will be cleared before the function returns. The response payload returns the status of the operation along with the name of the table that was cleared.

Parameters

authorization (str) –

No longer used. User can pass an empty string. The default value is ‘’.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • no\_error\_if\_not\_exists – If true and if the table specified in input parameter table\_name does not exist no error is returned. If false and if the table specified in input parameter table\_name does not exist then an error is returned. Allowed values are:

    • true

    • false

    The default value is ‘false’.

The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

table\_name (str) –

Value of input parameter table\_name for a given table, or ‘ALL CLEARED’ in case of clearing all tables.

info (dict of str to str) –

Additional information.

Raises

GPUdbException – –

Upon an error from the server.

collect\_statistics(column\_names=None, options={"{"}{"}"})

Collect statistics for a column(s) in a specified table.

Parameters

column\_names (list of str) –

List of one or more column names in input parameter table\_name for which to collect statistics (cardinality, mean value, etc.). A single entry of ‘\*’ expands to every collectable column on the table (geometry, vector, JSON, and array columns are skipped). The user can provide a single element (which will be automatically promoted to a list internally) or a list.

options (dict of str to str) –

Optional parameters. The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

table\_name (str) –

Value of input parameter table\_name.

column\_names (list of str) –

Value of input parameter column\_names.

info (dict of str to str) –

Additional information.

Raises

GPUdbException – –

Upon an error from the server.

create\_projection(column\_names=None, options={"{"}{"}"}, projection\_name=None)

Creates a new projection of an existing table. A projection represents a subset of the columns (potentially including derived columns) of a table.

For projection details and examples, see Projections. For limitations, see Projection Limitations and Cautions.

Window functions, which can perform operations like moving averages, are available through this endpoint as well as GPUdb.get\_records\_by\_column().

A projection can be created with a different shard key than the source table. By specifying shard\_key, the projection will be sharded according to the specified columns, regardless of how the source table is sharded. The source table can even be unsharded or replicated.

If input parameter table\_name is empty, selection is performed against a single-row virtual table. This can be useful in executing temporal (NOW()), identity (USER()), or constant-based functions (GEODIST(-77.11, 38.88, -71.06, 42.36)).

Parameters

column\_names (list of str) –

List of columns from input parameter table\_name to be included in the projection. Can include derived columns. Can be specified as aliased via the syntax ‘column\_name as alias’. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter projection\_name. If persist is false (or unspecified), then this is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_projection\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the projection as part of input parameter projection\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema for the projection. If the schema is non-existent, it will be automatically created. The default value is ‘’.

  • expression – An optional filter expression to be applied to the source table prior to the projection. The default value is ‘’.

  • is\_replicated – If true then the projection will be replicated even if the source table is not. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • offset – The number of initial results to skip (this can be useful for paging through the results). The default value is ‘0’.

  • limit – The number of records to keep. The default value is ‘-9999’.

  • order\_by – Comma-separated list of the columns to be sorted by; e.g. ‘timestamp asc, x desc’. The columns specified must be present in input parameter column\_names. If any alias is given for any column name, the alias must be used, rather than the original column name. The default value is ‘’.

  • chunk\_size – Indicates the number of records per chunk to be used for this projection.

  • chunk\_column\_max\_memory – Indicates the target maximum data size for each column in a chunk to be used for this projection.

  • chunk\_max\_memory – Indicates the target maximum data size for all columns in a chunk to be used for this projection.

  • create\_indexes – Comma-separated list of columns on which to create indexes on the projection. The columns specified must be present in input parameter column\_names. If any alias is given for any column name, the alias must be used, rather than the original column name.

  • ttl – Sets the TTL of the projection specified in input parameter projection\_name.

  • shard\_key – Comma-separated list of the columns to be sharded on; e.g. ‘column1, column2’. The columns specified must be present in input parameter column\_names. If any alias is given for any column name, the alias must be used, rather than the original column name. The default value is ‘’.

  • persist – If true, then the projection specified in input parameter projection\_name will be persisted and will not expire unless a ttl is specified. If false, then the projection will be an in-memory table and will expire unless a ttl is specified otherwise. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • preserve\_dict\_encoding – If true, then columns that were dict encoded in the source table will be dict encoded in the projection. Allowed values are:

    • true

    • false

    The default value is ‘true’.

  • retain\_partitions – Determines whether the created projection will retain the partitioning scheme from the source table. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • partition\_type – Partitioning scheme to use. Allowed values are:

  • partition\_keys – Comma-separated list of partition keys, which are the columns or column expressions by which records will be assigned to partitions defined by partition\_definitions.

  • partition\_definitions – Comma-separated list of partition definitions, whose format depends on the choice of partition\_type. See range partitioning, interval partitioning, list partitioning, hash partitioning, or series partitioning for example formats.

  • is\_automatic\_partition – If true, a new partition will be created for values which don’t fall into an existing partition. Currently only supported for list partitions. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • view\_id – ID of view of which this projection is a member. The default value is ‘’.

  • strategy\_definition – The tier strategy for the table and its columns.

  • compression\_codec – The default compression codec for the projection’s columns.

  • join\_window\_functions – If set, window functions which require a reshard will be computed separately and joined back together, if the width of the projection is greater than the join\_window\_functions\_threshold. The default value is ‘true’.

  • join\_window\_functions\_threshold – If the projection is greater than this width (in bytes), then window functions which require a reshard will be computed separately and joined back together. The default value is ‘’.

  • qualify\_filter – An optional filter expression applied to the projection after window function evaluation, equivalent to a SQL QUALIFY clause. May reference window function aliases as well as any other column in the projection. Rows for which the expression evaluates to false (or NULL) are removed from the projection. The default value is ‘’.

The default value is an empty dict ( {"{"}{"}"} ).

projection\_name (str) –

Name of the projection to be created, in \[schema\_name.]table\_name format, using standard name resolution rules and meeting table naming criteria.

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

create\_table\_monitor(options={"{"}{"}"})

Creates a monitor that watches for a single table modification event type (insert, update, or delete) on a particular table (identified by input parameter table\_name) and forwards event notifications to subscribers via ZMQ. After this call completes, subscribe to the returned output parameter topic\_id on the ZMQ table monitor port (default 9002). Each time an operation of the given type on the table completes, a multipart message is published for that topic; the first part contains only the topic ID, and each subsequent part contains one binary-encoded Avro object that corresponds to the event and can be decoded using output parameter type\_schema. The monitor will continue to run (regardless of whether or not there are any subscribers) until deactivated with GPUdb.clear\_table\_monitor().

For more information on table monitors, see Table Monitors.

Parameters

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • event – Type of modification event on the target table to be monitored by this table monitor. Allowed values are:

    • insert – Get notifications of new record insertions. The new row images are forwarded to the subscribers.

    • update – Get notifications of update operations. The modified row count information is forwarded to the subscribers.

    • delete – Get notifications of delete operations. The deleted row count information is forwarded to the subscribers.

    The default value is ‘insert’.

  • monitor\_id – ID to use for this monitor instead of a randomly generated one.

  • datasink\_name – Name of an existing data sink to send change data notifications to.

  • max\_consecutive\_failures – Maximum number of consecutive failed notification attempts before suspending the stream. A value of -1 (default) disables auto-suspend. This value is by rank and not overall.

  • failed\_notifications\_table\_name – Name of a table to which failed stream notifications are written when the stream is suspended. The database will attempt to send notifications persisted in this table when the stream is resumed. The table has the following columns: rank (long), job\_id (long), uuid (uuid), timestamp (timestamp), error\_msg (string), payload (bytes). Leave this option empty to disable persisting failed notification events.

  • destination – Destination for the output data in format ‘destination\_type://path\[:port]’. Supported destination types are ‘http’, ‘https’ and ‘kafka’.

  • kafka\_topic\_name – Name of the Kafka topic to publish to if destination in input parameter options is specified and is a Kafka broker.

  • increasing\_column – Column on subscribed table that will increase for new records (e.g., TIMESTAMP).

  • expression – Filter expression to limit records for notification.

  • join\_table\_names – A comma-separated list of tables (optionally with aliases) to include in the join. The monitored table input parameter table\_name must be included, representing only the newly inserted rows (deltas) since the last notification. Other tables can be any existing tables or views. Aliases can be used with the ‘table\_name as alias’ syntax.

  • join\_column\_names – A comma-separated list of columns or expressions to include from the joined tables. Column references can use table names or aliases defined in ‘join\_table\_names’. Each column can optionally be aliased using ‘as’. The selected columns will also appear in the notification output.

  • join\_expressions – Filter or join expressions to apply when combining the tables. Expressions are standard SQL-style conditions and can reference any table or alias listed in ‘join\_table\_names’. This corresponds to the WHERE clause of the underlying join, and can include conditions to filter the delta rows.

  • refresh\_method – Method controlling when the table monitor reports changes to the input parameter table\_name. Allowed values are:

    • on\_change – Report changes as they occur.

    • periodic – Report changes periodically at rate specified by refresh\_period.

    The default value is ‘on\_change’.

  • refresh\_period – When refresh\_method is periodic, specifies the period in seconds at which changes are reported.

  • refresh\_start\_time – When refresh\_method is periodic, specifies the first time at which changes are reported. Value is a datetime string with format ‘YYYY-MM-DD HH:MM:SS’.

The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

topic\_id (str) –

The ZMQ topic ID to subscribe to for table events.

table\_name (str) –

Value of input parameter table\_name.

type\_schema (str) –

JSON Avro schema of the table, for use in decoding published records.

info (dict of str to str) –

Additional information. Allowed keys are:

  • ttl – For insert\_table/delete\_table events, the ttl of the table.

  • insert\_topic\_id – The topic id for ‘insert’ event in input parameter options.

  • update\_topic\_id – The topic id for ‘update’ event in input parameter options.

  • delete\_topic\_id – The topic id for ‘delete’ event in input parameter options.

  • insert\_type\_schema – The JSON Avro schema of the table in output parameter table\_name.

  • update\_type\_schema – The JSON Avro schema for ‘update’ events.

  • delete\_type\_schema – The JSON Avro schema for ‘delete’ events.

The default value is an empty dict ( {"{"}{"}"} ).

Raises

GPUdbException – –

Upon an error from the server.

delete\_records(expressions=None, options={"{"}{"}"})

Deletes record(s) matching the provided criteria from the given table. The record selection criteria can either be one or more input parameter expressions (matching multiple records), a single record identified by record\_id options, or all records when using delete\_all\_records. Note that the three selection criteria are mutually exclusive. This operation cannot be run on a view. The operation is synchronous meaning that a response will not be available until the request is completely processed and all the matching records are deleted.

Parameters

expressions (list of str) –

A list of the actual predicates, one for each select; format should follow the guidelines provided here. Specifying one or more input parameter expressions is mutually exclusive to specifying record\_id in the input parameter options. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • global\_expression – An optional global expression to reduce the search space of the input parameter expressions. The default value is ‘’.

  • record\_id – A record ID identifying a single record, obtained at the time of insertion of the record or by calling GPUdb.get\_records\_from\_collection() with the return\_record\_ids option. This option cannot be used to delete records from replicated tables.

  • delete\_all\_records – If set to true, all records in the table will be deleted. If set to false, then the option is effectively ignored. Allowed values are:

    • true

    • false

    The default value is ‘false’.

The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

count\_deleted (long) –

Total number of records deleted across all expressions.

counts\_deleted (list of longs) –

Total number of records deleted per expression.

info (dict of str to str) –

Additional information.

Raises

GPUdbException – –

Upon an error from the server.

filter(expression=None, options={"{"}{"}"}, view\_name='')

Filters data based on the specified expression. The results are stored in a result set with the given input parameter view\_name.

For details see Expressions.

The response message contains the number of points for which the expression evaluated to be true, which is equivalent to the size of the result view.

Parameters

expression (str) –

The select expression to filter the specified table. For details see Expressions.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter view\_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_view\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the view as part of input parameter view\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema for the newly created view. If the schema is non-existent, it will be automatically created.

  • view\_id – View this filtered-view is part of. The default value is ‘’.

  • ttl – Sets the TTL of the view specified in input parameter view\_name.

The default value is an empty dict ( {"{"}{"}"} ).

view\_name (str) –

If provided, then this will be the name of the view containing the results, in \[schema\_name.]view\_name format, using standard name resolution rules and meeting table naming criteria. Must not be an already existing table or view. The default value is ‘’.

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

filter\_by\_area(x\_column\_name=None, x\_vector=None, y\_column\_name=None, y\_vector=None, options={"{"}{"}"}, view\_name='')

Calculates which objects from a table are within a named area of interest (NAI/polygon). The operation is synchronous, meaning that a response will not be returned until all the matching objects are fully available. The response payload provides the count of the resulting set. A new resultant set (view) which satisfies the input NAI restriction specification is created with the name input parameter view\_name passed in as part of the input.

Parameters

x\_column\_name (str) –

Name of the column containing the x values to be filtered.

x\_vector (list of floats) –

List of x coordinates of the vertices of the polygon representing the area to be filtered. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

y\_column\_name (str) –

Name of the column containing the y values to be filtered.

y\_vector (list of floats) –

List of y coordinates of the vertices of the polygon representing the area to be filtered. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter view\_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_view\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the view as part of input parameter view\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema for the newly created view. If the schema provided is non-existent, it will be automatically created.

The default value is an empty dict ( {"{"}{"}"} ).

view\_name (str) –

If provided, then this will be the name of the view containing the results, in \[schema\_name.]view\_name format, using standard name resolution rules and meeting table naming criteria. Must not be an already existing table or view. The default value is ‘’.

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

filter\_by\_area\_geometry(column\_name=None, x\_vector=None, y\_vector=None, options={"{"}{"}"}, view\_name='')

Calculates which geospatial geometry objects from a table intersect a named area of interest (NAI/polygon). The operation is synchronous, meaning that a response will not be returned until all the matching objects are fully available. The response payload provides the count of the resulting set. A new resultant set (view) which satisfies the input NAI restriction specification is created with the name input parameter view\_name passed in as part of the input.

Parameters

column\_name (str) –

Name of the geospatial geometry column to be filtered.

x\_vector (list of floats) –

List of x coordinates of the vertices of the polygon representing the area to be filtered. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

y\_vector (list of floats) –

List of y coordinates of the vertices of the polygon representing the area to be filtered. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter view\_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_view\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the view as part of input parameter view\_name and use GPUdb.create\_schema() to create the schema if non-existent] The schema for the newly created view. If the schema is non-existent, it will be automatically created.

The default value is an empty dict ( {"{"}{"}"} ).

view\_name (str) –

If provided, then this will be the name of the view containing the results, in \[schema\_name.]view\_name format, using standard name resolution rules and meeting table naming criteria. Must not be an already existing table or view. The default value is ‘’.

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

filter\_by\_box(x\_column\_name=None, min\_x=None, max\_x=None, y\_column\_name=None, min\_y=None, max\_y=None, options={"{"}{"}"}, view\_name='')

Calculates how many objects within the given table lie in a rectangular box. The operation is synchronous, meaning that a response will not be returned until all the objects are fully available. The response payload provides the count of the resulting set. A new resultant set which satisfies the input NAI restriction specification is also created when a input parameter view\_name is passed in as part of the input payload.

Parameters

x\_column\_name (str) –

Name of the column on which to perform the bounding box query. Must be a valid numeric column.

min\_x (float) –

Lower bound for the column chosen by input parameter x\_column\_name. Must be less than or equal to input parameter max\_x.

max\_x (float) –

Upper bound for input parameter x\_column\_name. Must be greater than or equal to input parameter min\_x.

y\_column\_name (str) –

Name of a column on which to perform the bounding box query. Must be a valid numeric column.

min\_y (float) –

Lower bound for input parameter y\_column\_name. Must be less than or equal to input parameter max\_y.

max\_y (float) –

Upper bound for input parameter y\_column\_name. Must be greater than or equal to input parameter min\_y.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter view\_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_view\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the view as part of input parameter view\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema for the newly created view. If the schema is non-existent, it will be automatically created.

The default value is an empty dict ( {"{"}{"}"} ).

view\_name (str) –

If provided, then this will be the name of the view containing the results, in \[schema\_name.]view\_name format, using standard name resolution rules and meeting table naming criteria. Must not be an already existing table or view. The default value is ‘’.

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

filter\_by\_box\_geometry(column\_name=None, min\_x=None, max\_x=None, min\_y=None, max\_y=None, options={"{"}{"}"}, view\_name='')

Calculates which geospatial geometry objects from a table intersect a rectangular box. The operation is synchronous, meaning that a response will not be returned until all the objects are fully available. The response payload provides the count of the resulting set. A new resultant set which satisfies the input NAI restriction specification is also created when a input parameter view\_name is passed in as part of the input payload.

Parameters

column\_name (str) –

Name of the geospatial geometry column to be filtered.

min\_x (float) –

Lower bound for the x-coordinate of the rectangular box. Must be less than or equal to input parameter max\_x.

max\_x (float) –

Upper bound for the x-coordinate of the rectangular box. Must be greater than or equal to input parameter min\_x.

min\_y (float) –

Lower bound for the y-coordinate of the rectangular box. Must be less than or equal to input parameter max\_y.

max\_y (float) –

Upper bound for the y-coordinate of the rectangular box. Must be greater than or equal to input parameter min\_y.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter view\_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_view\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the view as part of input parameter view\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema for the newly created view. If the schema provided is non-existent, it will be automatically created.

The default value is an empty dict ( {"{"}{"}"} ).

view\_name (str) –

If provided, then this will be the name of the view containing the results, in \[schema\_name.]view\_name format, using standard name resolution rules and meeting table naming criteria. Must not be an already existing table or view. The default value is ‘’.

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

filter\_by\_geometry(column\_name=None, input\_wkt='', operation=None, options={"{"}{"}"}, view\_name='')

Applies a geometry filter against a geospatial geometry column in a given table or view. The filtering geometry is provided by input parameter input\_wkt.

Parameters

column\_name (str) –

Name of the column to be used in the filter. Must be a geospatial geometry column.

input\_wkt (str) –

A geometry in WKT format that will be used to filter the objects in input parameter table\_name. The default value is ‘’.

operation (str) –

The geometric filtering operation to perform. Allowed values are:

  • contains – Matches records that contain the given WKT in input parameter input\_wkt, i.e. the given WKT is within the bounds of a record’s geometry.

  • crosses – Matches records that cross the given WKT.

  • disjoint – Matches records that are disjoint from the given WKT.

  • equals – Matches records that are the same as the given WKT.

  • intersects – Matches records that intersect the given WKT.

  • overlaps – Matches records that overlap the given WKT.

  • touches – Matches records that touch the given WKT.

  • within – Matches records that are within the given WKT.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter view\_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_view\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the view as part of input parameter view\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema for the newly created view. If the schema provided is non-existent, it will be automatically created.

The default value is an empty dict ( {"{"}{"}"} ).

view\_name (str) –

If provided, then this will be the name of the view containing the results, in \[schema\_name.]view\_name format, using standard name resolution rules and meeting table naming criteria. Must not be an already existing table or view. The default value is ‘’.

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

filter\_by\_list(column\_values\_map=None, options={"{"}{"}"}, view\_name='')

Calculates which records from a table have values in the given list for the corresponding column. The operation is synchronous, meaning that a response will not be returned until all the objects are fully available. The response payload provides the count of the resulting set. A new resultant set (view) which satisfies the input filter specification is also created if a input parameter view\_name is passed in as part of the request.

For example, if a type definition has the columns ‘x’ and ‘y’, then a filter by list query with the column map {"{"}“x”:\[“10.1”, “2.3”], “y”:\[“0.0”, “-31.5”, “42.0”]{"}"} will return the count of all data points whose x and y values match both in the respective x- and y-lists, e.g., “x = 10.1 and y = 0.0”, “x = 2.3 and y = -31.5”, etc. However, a record with “x = 10.1 and y = -31.5” or “x = 2.3 and y = 0.0” would not be returned because the values in the given lists do not correspond.

Parameters

column\_values\_map (dict of str to lists of str) –

List of values for the corresponding column in the table.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter view\_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_view\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the view as part of input parameter view\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema for the newly created view. If the schema provided is non-existent, it will be automatically created.

  • filter\_mode – String indicating the filter mode, either ‘in\_list’ or ‘not\_in\_list’. Allowed values are:

    • in\_list – The filter will match all items that are in the provided list(s).

    • not\_in\_list – The filter will match all items that are not in the provided list(s).

    The default value is ‘in\_list’.

The default value is an empty dict ( {"{"}{"}"} ).

view\_name (str) –

If provided, then this will be the name of the view containing the results, in \[schema\_name.]view\_name format, using standard name resolution rules and meeting table naming criteria. Must not be an already existing table or view. The default value is ‘’.

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

filter\_by\_radius(x\_column\_name=None, x\_center=None, y\_column\_name=None, y\_center=None, radius=None, options={"{"}{"}"}, view\_name='')

Calculates which objects from a table lie within a circle with the given radius and center point (i.e. circular NAI). The operation is synchronous, meaning that a response will not be returned until all the objects are fully available. The response payload provides the count of the resulting set. A new resultant set (view) which satisfies the input circular NAI restriction specification is also created if a input parameter view\_name is passed in as part of the request.

For track data, all track points that lie within the circle plus one point on either side of the circle (if the track goes beyond the circle) will be included in the result.

Parameters

x\_column\_name (str) –

Name of the column to be used for the x-coordinate (the longitude) of the center.

x\_center (float) –

Value of the longitude of the center. Must be within \[-180.0, 180.0]. The minimum allowed value is -180. The maximum allowed value is 180.

y\_column\_name (str) –

Name of the column to be used for the y-coordinate-the latitude-of the center.

y\_center (float) –

Value of the latitude of the center. Must be within \[-90.0, 90.0]. The minimum allowed value is -90. The maximum allowed value is 90.

radius (float) –

The radius of the circle within which the search will be performed. Must be a non-zero positive value. It is in meters; so, for example, a value of ‘42000’ means 42 km. The minimum allowed value is 0. The maximum allowed value is MAX\_INT.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter view\_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_view\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the view as part of input parameter view\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema which is to contain the newly created view. If the schema is non-existent, it will be automatically created.

The default value is an empty dict ( {"{"}{"}"} ).

view\_name (str) –

If provided, then this will be the name of the view containing the results, in \[schema\_name.]view\_name format, using standard name resolution rules and meeting table naming criteria. Must not be an already existing table or view. The default value is ‘’.

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

filter\_by\_radius\_geometry(column\_name=None, x\_center=None, y\_center=None, radius=None, options={"{"}{"}"}, view\_name='')

Calculates which geospatial geometry objects from a table intersect a circle with the given radius and center point (i.e. circular NAI). The operation is synchronous, meaning that a response will not be returned until all the objects are fully available. The response payload provides the count of the resulting set. A new resultant set (view) which satisfies the input circular NAI restriction specification is also created if a input parameter view\_name is passed in as part of the request.

Parameters

column\_name (str) –

Name of the geospatial geometry column to be filtered.

x\_center (float) –

Value of the longitude of the center. Must be within \[-180.0, 180.0]. The minimum allowed value is -180. The maximum allowed value is 180.

y\_center (float) –

Value of the latitude of the center. Must be within \[-90.0, 90.0]. The minimum allowed value is -90. The maximum allowed value is 90.

radius (float) –

The radius of the circle within which the search will be performed. Must be a non-zero positive value. It is in meters; so, for example, a value of ‘42000’ means 42 km. The minimum allowed value is 0. The maximum allowed value is MAX\_INT.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter view\_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_view\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the view as part of input parameter view\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema for the newly created view. If the schema provided is non-existent, it will be automatically created.

The default value is an empty dict ( {"{"}{"}"} ).

view\_name (str) –

If provided, then this will be the name of the view containing the results, in \[schema\_name.]view\_name format, using standard name resolution rules and meeting table naming criteria. Must not be an already existing table or view. The default value is ‘’.

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

filter\_by\_range(column\_name=None, lower\_bound=None, upper\_bound=None, options={"{"}{"}"}, view\_name='')

Calculates which objects from a table have a column that is within the given bounds. An object from the table identified by input parameter table\_name is added to the view input parameter view\_name if its column is within \[input parameter lower\_bound, input parameter upper\_bound] (inclusive). The operation is synchronous. The response provides a count of the number of objects which passed the bound filter. Although this functionality can also be accomplished with the standard filter function, it is more efficient.

For track objects, the count reflects how many points fall within the given bounds (which may not include all the track points of any given track).

Parameters

column\_name (str) –

Name of a column on which the operation would be applied.

lower\_bound (float) –

Value of the lower bound (inclusive).

upper\_bound (float) –

Value of the upper bound (inclusive).

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter view\_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_view\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the view as part of input parameter view\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema for the newly created view. If the schema is non-existent, it will be automatically created.

The default value is an empty dict ( {"{"}{"}"} ).

view\_name (str) –

If provided, then this will be the name of the view containing the results, in \[schema\_name.]view\_name format, using standard name resolution rules and meeting table naming criteria. Must not be an already existing table or view. The default value is ‘’.

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

filter\_by\_series(track\_id=None, target\_track\_ids=None, options={"{"}{"}"}, view\_name='')

Filters objects matching all points of the given track (works only on track type data). It allows users to specify a particular track to find all other points in the table that fall within specified ranges (spatial and temporal) of all points of the given track. Additionally, the user can specify another track to see if the two intersect (or go close to each other within the specified ranges). The user also has the flexibility of using different metrics for the spatial distance calculation: Euclidean (flat geometry) or Great Circle (spherical geometry to approximate the Earth’s surface distances). The filtered points are stored in a newly created result set. The return value of the function is the number of points in the resultant set (view).

This operation is synchronous, meaning that a response will not be returned until all the objects are fully available.

Parameters

track\_id (str) –

The ID of the track which will act as the filtering points. Must be an existing track within the given table.

target\_track\_ids (list of str) –

Up to one track ID to intersect with the “filter” track. If any provided, it must be an valid track ID within the given set. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter view\_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_view\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the view as part of input parameter view\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema for the newly created view. If the schema is non-existent, it will be automatically created.

  • spatial\_radius – A positive number passed as a string representing the radius of the search area centered around each track point’s geospatial coordinates. The value is interpreted in meters. Required parameter. The minimum allowed value is ‘0’.

  • time\_radius – A positive number passed as a string representing the maximum allowable time difference between the timestamps of a filtered object and the given track’s points. The value is interpreted in seconds. Required parameter. The minimum allowed value is ‘0’.

  • spatial\_distance\_metric – A string representing the coordinate system to use for the spatial search criteria. Acceptable values are ‘euclidean’ and ‘great\_circle’. Optional parameter; default is ‘euclidean’. Allowed values are:

    • euclidean

    • great\_circle

The default value is an empty dict ( {"{"}{"}"} ).

view\_name (str) –

If provided, then this will be the name of the view containing the results, in \[schema\_name.]view\_name format, using standard name resolution rules and meeting table naming criteria. Must not be an already existing table or view. The default value is ‘’.

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

filter\_by\_string(expression=None, mode=None, column\_names=None, options={"{"}{"}"}, view\_name='')

Calculates which objects from a table or view match a string expression for the given string columns. Setting case\_sensitive can modify case sensitivity in matching for all modes except search. For search mode details and limitations, see Full Text Search.

Parameters

expression (str) –

The expression with which to filter the table.

mode (str) –

The string filtering mode to apply. See below for details. Allowed values are:

  • search – Full text search query with wildcards and boolean operators. Note that for this mode, no column can be specified in input parameter column\_names; all string columns of the table that have text search enabled will be searched.

  • equals – Exact whole-string match (accelerated).

  • contains – Partial substring match (not accelerated). If the column is a string type (non-charN) and the number of records is too large, it will return 0.

  • starts\_with – Strings that start with the given expression (not accelerated). If the column is a string type (non-charN) and the number of records is too large, it will return 0.

  • regex – Full regular expression search (not accelerated). If the column is a string type (non-charN) and the number of records is too large, it will return 0.

column\_names (list of str) –

List of columns on which to apply the filter. Ignored for search mode. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter view\_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_view\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the view as part of input parameter view\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema for the newly created view. If the schema is non-existent, it will be automatically created.

  • case\_sensitive – If false then string filtering will ignore case. Does not apply to search mode. Allowed values are:

    • true

    • false

    The default value is ‘true’.

The default value is an empty dict ( {"{"}{"}"} ).

view\_name (str) –

If provided, then this will be the name of the view containing the results, in \[schema\_name.]view\_name format, using standard name resolution rules and meeting table naming criteria. Must not be an already existing table or view. The default value is ‘’.

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

filter\_by\_table(column\_name=None, source\_table\_name=None, source\_table\_column\_name=None, options={"{"}{"}"}, view\_name='')

Filters objects in one table based on objects in another table. The user must specify matching column types from the two tables (i.e. the target table from which objects will be filtered and the source table based on which the filter will be created); the column names need not be the same. If a input parameter view\_name is specified, then the filtered objects will then be put in a newly created view. The operation is synchronous, meaning that a response will not be returned until all objects are fully available in the result view. The return value contains the count (i.e. the size) of the resulting view.

Parameters

column\_name (str) –

Name of the column by whose value the data will be filtered from the table designated by input parameter table\_name.

source\_table\_name (str) –

Name of the table whose data will be compared against in the table called input parameter table\_name, in \[schema\_name.]table\_name format, using standard name resolution rules. Must be an existing table.

source\_table\_column\_name (str) –

Name of the column in the input parameter source\_table\_name whose values will be used as the filter for table input parameter table\_name. Must be a geospatial geometry column if in ‘spatial’ mode; otherwise, Must match the type of the input parameter column\_name.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter view\_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_view\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the view as part of input parameter view\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema for the newly created view. If the schema is non-existent, it will be automatically created.

  • filter\_mode – String indicating the filter mode, either in\_table or not\_in\_table. Allowed values are:

    • in\_table

    • not\_in\_table

    The default value is ‘in\_table’.

  • mode – Mode - should be either spatial or normal. Allowed values are:

    • normal

    • spatial

    The default value is ‘normal’.

  • buffer – Buffer size, in meters. Only relevant for spatial mode. The default value is ‘0’.

  • buffer\_method – Method used to buffer polygons. Only relevant for spatial mode. Allowed values are:

    • normal

    • geos – Use geos 1 edge per corner algorithm.

    The default value is ‘normal’.

  • max\_partition\_size – Maximum number of points in a partition. Only relevant for spatial mode. The default value is ‘0’.

  • max\_partition\_score – Maximum number of points \* edges in a partition. Only relevant for spatial mode. The default value is ‘8000000’.

  • x\_column\_name – Name of column containing x value of point being filtered in spatial mode. The default value is ‘x’.

  • y\_column\_name – Name of column containing y value of point being filtered in spatial mode. The default value is ‘y’.

The default value is an empty dict ( {"{"}{"}"} ).

view\_name (str) –

If provided, then this will be the name of the view containing the results, in \[schema\_name.]view\_name format, using standard name resolution rules and meeting table naming criteria. Must not be an already existing table or view. The default value is ‘’.

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

filter\_by\_value(is\_string=None, value=0, value\_str='', column\_name=None, options={"{"}{"}"}, view\_name='')

Calculates which objects from a table has a particular value for a particular column. The input parameters provide a way to specify either a String or a Double valued column and a desired value for the column on which the filter is performed. The operation is synchronous, meaning that a response will not be returned until all the objects are fully available. The response payload provides the count of the resulting set. A new result view which satisfies the input filter restriction specification is also created with a view name passed in as part of the input payload. Although this functionality can also be accomplished with the standard filter function, it is more efficient.

Parameters

is\_string (bool) –

Indicates whether the value being searched for is string or numeric.

value (float) –

The value to search for. The default value is 0.

value\_str (str) –

The string value to search for. The default value is ‘’.

column\_name (str) –

Name of a column on which the filter by value would be applied.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • create\_temp\_table – If true, a unique temporary table name will be generated in the sys\_temp schema and used in place of input parameter view\_name. This is always allowed even if the caller does not have permission to create tables. The generated name is returned in qualified\_view\_name. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • collection\_name – \[DEPRECATED–please specify the containing schema for the view as part of input parameter view\_name and use GPUdb.create\_schema() to create the schema if non-existent] Name of a schema for the newly created view. If the schema is non-existent, it will be automatically created.

The default value is an empty dict ( {"{"}{"}"} ).

view\_name (str) –

If provided, then this will be the name of the view containing the results, in \[schema\_name.]view\_name format, using standard name resolution rules and meeting table naming criteria. Must not be an already existing table or view. The default value is ‘’.

Returns

A read-only GPUdbTable object.

Raises

GPUdbException – –

Upon an error from the server.

lock\_table(lock\_type='status', options={"{"}{"}"})

Manages global access to a table’s data. By default a table has a input parameter lock\_type of read\_write, indicating all operations are permitted. A user may request a read\_only or a write\_only lock, after which only read or write operations, respectively, are permitted on the table until the lock is removed. When input parameter lock\_type is no\_access then no operations are permitted on the table. The lock status can be queried by setting input parameter lock\_type to status.

Parameters

lock\_type (str) –

The type of lock being applied to the table. Setting it to status will return the current lock status of the table without changing it. Allowed values are:

  • status – Show locked status.

  • no\_access – Allow no read/write operations.

  • read\_only – Allow only read operations.

  • write\_only – Allow only write operations.

  • read\_write – Allow all read/write operations.

The default value is ‘status’.

options (dict of str to str) –

Optional parameters. The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

lock\_type (str) –

Returns the lock state of the table.

info (dict of str to str) –

Additional information.

Raises

GPUdbException – –

Upon an error from the server.

show\_table(options={"{"}{"}"})

Retrieves detailed information about a table, view, or schema, specified in input parameter table\_name. If the supplied input parameter table\_name is a schema the call can return information about either the schema itself or the tables and views it contains. If input parameter table\_name is empty, information about all schemas will be returned.

If the option get\_sizes is set to true, then the number of records in each table is returned (in output parameter sizes and output parameter full\_sizes), along with the total number of objects across all requested tables (in output parameter total\_size and output parameter total\_full\_size).

For a schema, setting the show\_children option to false returns only information about the schema itself; setting show\_children to true returns a list of tables and views contained in the schema, along with their corresponding detail.

To retrieve a list of every table, view, and schema in the database, set input parameter table\_name to ‘\*’ and show\_children to true. When doing this, the returned output parameter total\_size and output parameter total\_full\_size will not include the sizes of non-base tables (e.g., filters, views, joins, etc.).

Parameters

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • dependencies – Include view dependencies in the output. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • force\_synchronous – If true then the table sizes will wait for read lock before returning. Allowed values are:

    • true

    • false

    The default value is ‘true’.

  • get\_access\_data – If true then data about the last read, write, alter and create will be returned. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • get\_cached\_sizes – If true then the number of records in each table, along with a cumulative count, will be returned; blank, otherwise. This version will return the sizes cached at rank 0, which may be stale if there is a multihead insert occurring. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • get\_sizes – If true then the number of records in each table, along with a cumulative count, will be returned; blank, otherwise. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • referencing\_materialized\_views – Include materialized views using this table as a source in the output. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • skip\_additional\_info – If true then the response will not populate the additional\_info field. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • no\_error\_if\_not\_exists – If false will return an error if the provided input parameter table\_name does not exist. If true then it will return an empty result. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • skip\_temp\_schemas – If true then the table list will not include tables from SYS\_TEMP and other system temporary schemas. This is the default behavior for non-admin users. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • show\_children – If input parameter table\_name is a schema, then true will return information about the tables and views in the schema, and false will return information about the schema itself. If input parameter table\_name is a table or view, show\_children must be false. If input parameter table\_name is empty, then show\_children must be true. Allowed values are:

    • true

    • false

    The default value is ‘true’.

  • get\_column\_info – If true then column info (memory usage, etc) will be returned. Allowed values are:

    • true

    • false

    The default value is ‘false’.

The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

table\_name (str) –

Value of input parameter table\_name.

table\_names (list of str) –

If input parameter table\_name is a table or view, then the single element of the array is input parameter table\_name. If input parameter table\_name is a schema and show\_children is set to true, then this array is populated with the names of all tables and views in the given schema; if show\_children is false, then this array will only include the schema name itself. If input parameter table\_name is an empty string, then the array contains the names of all tables in the user’s default schema.

table\_descriptions (list of lists of str) –

List of descriptions for the respective tables in output parameter table\_names. Allowed values are:

  • COLLECTION

  • JOIN

  • LOGICAL\_EXTERNAL\_TABLE

  • LOGICAL\_VIEW

  • MATERIALIZED\_EXTERNAL\_TABLE

  • MATERIALIZED\_VIEW

  • MATERIALIZED\_VIEW\_MEMBER

  • MATERIALIZED\_VIEW\_UNDER\_CONSTRUCTION

  • REPLICATED

  • RESULT\_TABLE

  • SCHEMA

  • VIEW

type\_ids (list of str) –

Type IDs of the respective tables in output parameter table\_names.

type\_schemas (list of str) –

Type schemas of the respective tables in output parameter table\_names.

type\_labels (list of str) –

Type labels of the respective tables in output parameter table\_names.

properties (list of dicts of str to lists of str) –

Property maps of the respective tables in output parameter table\_names.

additional\_info (list of dicts of str to str) –

Additional information about the respective tables in output parameter table\_names. Allowed keys are:

  • request\_avro\_type – Method by which this table was created. Allowed values are:

    • create\_table

    • create\_projection

    • create\_union

  • request\_avro\_json – The JSON representation of request creating this table. The default value is ‘’.

  • protected – No longer used. Indicated whether the respective table was protected or not. Allowed values are:

    • true

    • false

  • record\_bytes – The number of in-memory bytes per record which is the sum of the byte sizes of all columns with property ‘data’.

  • total\_bytes – The total size in bytes of all data stored in the table.

  • collection\_names – \[DEPRECATED–use schema\_name instead] This will now contain the name of the schema for the table. There can only be one schema for a table.

  • schema\_name – The name of the schema for the table. There can only be one schema for a table.

  • table\_ttl – The value of the time-to-live setting. Not present for schemas.

  • remaining\_table\_ttl – The remaining time-to-live, in minutes, before the respective table expires (-1 if it will never expire). Not present for schemas.

  • primary\_key\_type – The primary key type of the table (if it has a primary key). Allowed values are:

    • memory – In-memory primary key.

    • disk – On-disk primary key.

  • foreign\_keys – Semicolon-separated list of foreign keys, of the format ‘source\_column references target\_table(primary\_key\_column)’. Not present for schemas. The default value is ‘’.

  • foreign\_shard\_key – Foreign shard key description of the format: \ references \ from \(\). Not present for schemas. The default value is ‘’.

  • partition\_type – Partitioning scheme used for this table. Allowed values are:

    The default value is ‘NONE’.

  • partition\_keys – Comma-separated list of partition keys. The default value is ‘’.

  • partition\_definitions – Comma-separated list of partition definitions, whose format depends on the partition\_type. See partitioning documentation for details. The default value is ‘’.

  • is\_automatic\_partition – True if partitions will be created for LIST VALUES which don’t fall into existing partitions. The default value is ‘’.

  • attribute\_indexes – Semicolon-separated list of indexes. For column (attribute) indexes, only the indexed column name will be listed. For other index types, the index type will be listed with the colon-delimited indexed column(s) and the comma-delimited index option(s) using the form: \@\@\. Not present for schemas. The default value is ‘’.

  • column\_info – JSON-encoded string representing a map of column name to information including memory usage if the get\_column\_info option is true. The default value is ‘’.

  • global\_access\_mode – Returns the global access mode (i.e. lock status) for the table. Allowed values are:

    • no\_access – No read/write operations are allowed on this table.

    • read\_only – Only read operations are allowed on this table.

    • write\_only – Only write operations are allowed on this table.

    • read\_write – All read/write operations are allowed on this table.

  • view\_table\_name – For materialized view the name of the view this member table is part of - if same as the table\_name then this is the root of the view. The default value is ‘’.

  • is\_view\_persisted – True if the view named view\_table\_name is persisted - reported for each view member. Means method of recreating this member is saved - not the members data. The default value is ‘’.

  • is\_dirty – True if some input table of the materialized view that affects this member table has been modified since the last refresh. The default value is ‘’.

  • refresh\_method – For materialized view current refresh\_method - one of manual, periodic, on\_change. The default value is ‘’.

  • refresh\_start\_time – For materialized view with periodic refresh\_method the initial datetime string that periodic refreshes began. The default value is ‘’.

  • refresh\_stop\_time – Time at which the periodic view refresh stops. The default value is ‘’.

  • refresh\_period – For materialized view with periodic refresh\_method the current refresh period in seconds. The default value is ‘’.

  • last\_refresh\_time – For materialized view the datetime string indicating the last time the view was refreshed. The default value is ‘’.

  • next\_refresh\_time – For materialized view with periodic refresh\_method a datetime string indicating the next time the view is to be refreshed. The default value is ‘’.

  • user\_chunk\_size – User-specified number of records per chunk, if provided at table creation time. The default value is ‘’.

  • user\_chunk\_column\_max\_memory – User-specified target max bytes per column in a chunk, if provided at table creation time. The default value is ‘’.

  • user\_chunk\_max\_memory – User-specified target max bytes for all columns in a chunk, if provided at table creation time. The default value is ‘’.

  • owner\_resource\_group – Name of the owner resource group. The default value is ‘’.

  • alternate\_shard\_keys – Semicolon-separated list of shard keys that were equated in joins (applicable for join tables). The default value is ‘’.

  • datasource\_subscriptions – Semicolon-separated list of datasource names the table has subscribed to. The default value is ‘’.

  • null\_modifying\_columns – Comma-separated list of null modifying column names. The default value is ‘’.

  • compression\_codec – Default compression codec for the table. The default value is ‘’.

  • created\_by – User that created this table or view. The default value is ‘’.

  • created\_time – Time (UTC) when this table or view was created. The default value is ‘’.

  • last\_read\_by – User that last read this table or view. The default value is ‘’.

  • last\_read\_time – Time (UTC) when this table or view was last read. The default value is ‘’.

  • read\_count – Count of times this table or view was read. The default value is ‘’.

  • last\_write\_by – User that last wrote to this table. The default value is ‘’.

  • last\_write\_time – Time (UTC) when this table was last written. The default value is ‘’.

  • write\_count – Count of times this table was written. The default value is ‘’.

  • last\_alter\_by – User that last altered this table or view. The default value is ‘’.

  • last\_alter\_time – Time (UTC) when this table or view was last altered. The default value is ‘’.

  • alter\_count – Count of times this table or view was altered. The default value is ‘’.

sizes (list of longs) –

If get\_sizes is true, an array containing the number of records of each corresponding table in output parameter table\_names. Otherwise, an empty array.

full\_sizes (list of longs) –

If get\_sizes is true, an array containing the number of records of each corresponding table in output parameter table\_names (same values as output parameter sizes). Otherwise, an empty array.

join\_sizes (list of floats) –

If get\_sizes is true, an array containing the number of unfiltered records in the cross product of the sub-tables of each corresponding join-table in output parameter table\_names. For simple tables, this number will be the same as output parameter sizes. For join-tables, this value gives the number of joined-table rows that must be processed by any aggregate functions operating on the table. Otherwise, (if get\_sizes is false), an empty array.

total\_size (long) –

If get\_sizes is true, the sum of the elements of output parameter sizes. Otherwise, -1.

total\_full\_size (long) –

If get\_sizes is true, the sum of the elements of output parameter full\_sizes (same value as output parameter total\_size). Otherwise, -1.

info (dict of str to str) –

Additional information.

Raises

GPUdbException – –

Upon an error from the server.

update\_records(expressions=None, new\_values\_maps=None, records\_to\_insert=\[], records\_to\_insert\_str=\[], record\_encoding='binary', options={"{"}{"}"})

Runs multiple predicate-based updates in a single call. With the list of given expressions, any matching record’s column values will be updated as provided in input parameter new\_values\_maps. There is also an optional ‘upsert’ capability where if a particular predicate doesn’t match any existing record, then a new record can be inserted.

Note that this operation can only be run on an original table and not on a result view.

This operation can update primary key values. By default only ‘pure primary key’ predicates are allowed when updating primary key values. If the primary key for a table is the column ‘attr1’, then the operation will only accept predicates of the form: “attr1 == ‘foo’” if the attr1 column is being updated. For a composite primary key (e.g. columns ‘attr1’ and ‘attr2’) then this operation will only accept predicates of the form: “(attr1 == ‘foo’) and (attr2 == ‘bar’)”. Meaning, all primary key columns must appear in an equality predicate in the expressions. Furthermore each ‘pure primary key’ predicate must be unique within a given request. These restrictions can be removed by utilizing some available options through input parameter options.

The update\_on\_existing\_pk option specifies the record primary key collision policy for tables with a primary key, while ignore\_existing\_pk specifies the record primary key collision error-suppression policy when those collisions result in the update being rejected. Both are ignored on tables with no primary key.

Parameters

expressions (list of str) –

A list of the actual predicates, one for each update; format should follow the guidelines here. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

new\_values\_maps (list of dicts of str to optional str) –

List of new values for the matching records. Each element is a map with (key, value) pairs where the keys are the names of the columns whose values are to be updated; the values are the new values. The number of elements in the list should match the length of input parameter expressions. The user can provide a single element (which will be automatically promoted to a list internally) or a list.

records\_to\_insert (list of bytes) –

An optional list of new binary-avro encoded records to insert, one for each update. If one of input parameter expressions does not yield a matching record to be updated, then the corresponding element from this list will be added to the table. The default value is an empty list ( \[] ). The user can provide a single element (which will be automatically promoted to a list internally) or a list.

records\_to\_insert\_str (list of str) –

An optional list of JSON encoded objects to insert, one for each update, to be added if the particular update did not match any objects. The default value is an empty list ( \[] ). The user can provide a single element (which will be automatically promoted to a list internally) or a list.

record\_encoding (str) –

Identifies which of input parameter records\_to\_insert and input parameter records\_to\_insert\_str should be used. Allowed values are:

  • binary

  • json

The default value is ‘binary’.

options (dict of str to str) –

Optional parameters. Allowed keys are:

  • global\_expression – An optional global expression to reduce the search space of the predicates listed in input parameter expressions. The default value is ‘’.

  • bypass\_safety\_checks – When set to true, all predicates are available for primary key updates. Keep in mind that it is possible to destroy data in this case, since a single predicate may match multiple objects (potentially all of records of a table), and then updating all of those records to have the same primary key will, due to the primary key uniqueness constraints, effectively delete all but one of those updated records. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • update\_on\_existing\_pk – Specifies the record collision policy for updating a table with a primary key. There are two ways that a record collision can occur.

    The first is an “update collision”, which happens when the update changes the value of the updated record’s primary key, and that new primary key already exists as the primary key of another record in the table.

    The second is an “insert collision”, which occurs when a given filter in input parameter expressions finds no records to update, and the alternate insert record given in input parameter records\_to\_insert (or input parameter records\_to\_insert\_str) contains a primary key matching that of an existing record in the table.

    If update\_on\_existing\_pk is set to true, “update collisions” will result in the existing record collided into being removed and the record updated with values specified in input parameter new\_values\_maps taking its place; “insert collisions” will result in the collided-into record being updated with the values in input parameter records\_to\_insert / input parameter records\_to\_insert\_str (if given).

    If set to false, the existing collided-into record will remain unchanged, while the update will be rejected and the error handled as determined by ignore\_existing\_pk. If the specified table does not have a primary key, then this option has no effect. Allowed values are:

    • true – Overwrite the collided-into record when updating a record’s primary key or inserting an alternate record causes a primary key collision between the record being updated/inserted and another existing record in the table

    • false – Reject updates which cause primary key collisions between the record being updated/inserted and an existing record in the table

    The default value is ‘false’.

  • ignore\_existing\_pk – Specifies the record collision error-suppression policy for updating a table with a primary key, only used when primary key record collisions are rejected (update\_on\_existing\_pk is false). If set to true, any record update that is rejected for resulting in a primary key collision with an existing table record will be ignored with no error generated. If false, the rejection of any update for resulting in a primary key collision will cause an error to be reported. If the specified table does not have a primary key or if update\_on\_existing\_pk is true, then this option has no effect. Allowed values are:

    • true – Ignore updates that result in primary key collisions with existing records.

    • false – Treat as errors any updates that result in primary key collisions with existing records.

    The default value is ‘false’.

  • update\_partition – Force qualifying records to be deleted and reinserted so their partition membership will be reevaluated. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • enable\_inplace\_updates – If set to true, qualifying records are modified in place. If set to false, they are updated by deleting the existing record and inserting a replacement (delete and insert), which prevents the change from being reflected in dependent materialized views until they are refreshed. Allowed values are:

    • true

    • false

    The default value is ‘true’.

  • truncate\_strings – If set to true, any strings which are too long for their charN string fields will be truncated to fit. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • use\_expressions\_in\_new\_values\_maps – When set to true, all new values in input parameter new\_values\_maps are considered as expression values. When set to false, all new values in input parameter new\_values\_maps are considered as constants. NOTE: When true, string constants will need to be quoted to avoid being evaluated as expressions. Allowed values are:

    • true

    • false

    The default value is ‘false’.

  • record\_id – ID of a single record to be updated (returned in the call to GPUdb.insert\_records() or GPUdb.get\_records\_from\_collection()).

The default value is an empty dict ( {"{"}{"}"} ).

Returns

The response from the server which is a dict containing the –

following entries–

count\_updated (long) –

Total number of records updated.

counts\_updated (list of longs) –

Total number of records updated per predicate in input parameter expressions.

count\_inserted (long) –

Total number of records inserted (due to expressions not matching any existing records).

counts\_inserted (list of longs) –

Total number of records inserted per predicate in input parameter expressions (will be either 0 or 1 for each expression).

info (dict of str to str) –

Additional information.

Raises

GPUdbException – –

Upon an error from the server.