Migrating To Kinetica 7.1

Name Resolution

A table or view can be addressed by qualified name, prefixing the name with the name of its containing schema, separated by a dot; e.g.:

<schema name>.<table/view name>

A table or view referenced without a schema will be looked for in the user's default schema, if assigned; effectively, a reference like this:

<table name>

is resolved like this:

<default schema name>.<table name>

This scheme can be overridden by specifying a schema to use in the request:

• Using the native API, a current_schema option can be passed to the /execute/sql endpoint

• In an ODBC/JDBC SQL session, setting the current schema will cause all subsequent calls in the session to use that schema for unqualified tables or views:

  1	SET CURRENT SCHEMA [<schema name>]

Top-Level Tables and the Home & Default Schemas

Within Kinetica 7.1, all tables & views will be required to be contained within a schema. All tables & views created in a previous release that are not contained within a schema will automatically be moved into the new home schema.

Permissions on the moved database objects will be retained, and this home schema will be assigned as the default schema to all existing users. This will ensure that any existing unqualified references to top-level tables or views will still resolve correctly in Kinetica 7.1.

In addition, new users not assigned a default schema explicitly will be assigned this home schema as their default schema. This automatic assignment includes LDAP users created automatically by the system. In this way, users created both before and after an upgrade will have the same default name resolution for pre-upgrade tables & views.

Any user can be assigned a different default schema afterwards, via /alter/user.

Unqualified Within-Schema References

While the use of a default schema can help with references to top-level tables made in Kinetica 7.0, and the setting of a current schema may help with references to individual within-schema tables, some queries may need to be modified to make qualified references to those tables in Kinetica 7.1.

A simple case of this occurs with a join that makes unqualified references to two tables that reside in different schemas. In this case, setting a current schema will not allow unqualified access to both tables. The references will need to be modified to have the schemas made explicit. For instance, consider a join between two tables in different schemas, table_a in schema_a and table_b in schema_b, where the references were previously unqualified:

1
2
3
4
5
6

SELECT a.name, b.desc
FROM
    table_a a,
    table_b b
WHERE
    a.b_id = b.id

This will now need qualified schema references:

1
2
3
4
5
6

SELECT a.name, b.desc
FROM
    schema_a.table_a a,
    schema_b.table_b b
WHERE
    a.b_id = b.id

Any queries of this type will need to make qualified references to their tables, and any views containing queries of this type will need to be modified in the same way and recreated.

A similar case occurs within SQL procedures, which may reference tables/views in multiple schemas. These will also need to be modified to make qualified references to those tables/views and then recreated.

TTL

Schemas do not support having a TTL assigned to them. TTLs can be assigned directly to the tables & views within a schema instead.

Built-In Schemas

The set of built-in schemas has changed as shown in the following table.

Name Resolution

The name resolution rules for tables and views not prefixed with a schema name have changed. See above for details.

Default Schema

The home schema will be assigned as the default schema to existing users during the Kinetica 7.1 upgrade process. Users created after upgrade will be assigned the home schema as their default schema, as well. Any user can be assigned a different default schema afterwards, via ALTER USER.