Create Join Table

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.

Input Parameter Description

Name	Type	Description
join_table_name	string	Name of the join table to be created. Has the same naming restrictions as tables.
table_names	array of strings	The list of table names composing the join. Corresponds to a SQL statement FROM clause.
column_names	array of strings	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.
expressions	array of strings	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 array ( [] ).
options	map of string to strings	Optional parameters. The default value is an empty map ( {} ). Supported Parameters (keys) Parameter Description collection_name Name of a collection which is to contain the join. If the collection provided is non-existent, the collection will be automatically created. If empty, then the join will be at the top level. The default value is ''. max_query_dimensions The maximum number of tables in a join that can be accessed by a query and are not equated by a foreign-key to primary-key equality predicate optimize_lookups Use more memory to speed up the joining of tables. The supported values are: true false refresh_method Method by which the join can be refreshed when the data in underlying member tables have changed. Supported Values Description manual refresh only occurs when manually requested by calling this endpoint with refresh option set to refresh or full_refresh on_query incrementally refresh (refresh just those records added) whenever a new query is issued and new data is inserted into the base table. A full refresh of all the records occurs when a new query is issued and there have been inserts to any non-base-tables since the last query. TTL will be set to not expire; any ttl specified will be ignored. on_insert incrementally refresh (refresh just those records added) whenever new data is inserted into a base table. A full refresh of all the records occurs when a new query is issued and there have been inserts to any non-base-tables since the last query. TTL will be set to not expire; any ttl specified will be ignored. refresh Do a manual refresh of the join if it exists - throws an error otherwise Supported Values Description no_refresh don't refresh refresh incrementally refresh (refresh just those records added) if new data has been inserted into the base table. A full refresh of all the records occurs if there have been inserts to any non-base-tables since the last refresh full_refresh always refresh even if no new records have been added. Only refresh method guaranteed to do a full refresh (refresh all the records) if a delete or update has occurred since the last refresh. ttl Sets the TTL of the join table specified in input parameter join_table_name. Ignored if refresh_method is either on_insert or on_query. 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 show_table. optimization needed for large overlapped equi-join stencils. The default value is 'false'. chunk_size Maximum size of a joined-chunk for this table. Defaults to the gpudb.conf file chunk size allow_right_primary_key_join When true allows right joins from a key to a primary key to be done as primary key joins. Such a join table cannot be joined to other join tables. When false the right join shall be done as an equi-join. The default value is 'false'.

Output Parameter Description

Name	Type	Description
join_table_name	string	Value of input parameter join_table_name.
count	long	The number of records in the join table filtered by the given select expression.