Creating Synonyms With Snowflake Cloud Data Warehouse (JDBC)

 

Synonyms define unique names, or aliases, for each Snowflake Cloud Data Warehouse table that is accessible from a server. Synonyms are useful because they hide the location and identity of the underlying data source from client applications. They also provide support for extended metadata features of the server, such as virtual fields and additional security mechanisms.

Using synonyms allows an object to be moved or renamed while enabling client applications to continue functioning without modification. The only modification required is a redefinition of the synonym on the server. The result of creating a synonym is a Master File and Access File based on a given Snowflake Cloud Data Warehouse table.

Create a Synonym

    Procedure
  1. From the WebFOCUS Reporting Server browser interface Application page, click Get Data.
  2. On the Configured Adapters section of the page, in Simple Mode, right-click an adapter and click Show Connections. Right-click a connection.

    Depending on the type of adapter you choose, one of the following options appears on the context menu.

    • Show DBMS objects. This option opens the page for selecting synonym objects and properties.
    • Create metadata objects. This option opens the page for selecting synonym objects and properties.
    • Show files. This option opens a file picker. After you choose a file of the correct type, the page for selecting synonym objects and properties opens.
    • Show local files. This option opens a file picker. After you choose a file of the correct type, the page for selecting synonym objects and properties opens.
    • Show topics. This option opens the page for selecting synonym objects and properties for topics within the environment.
  3. Enter values for the parameters required by the adapter as described in the chapter for your adapter.
  4. After entering the parameter values, click Add.

    This button may be labeled Next, Create Synonym, Create Base Synonyms, Create Cluster Synonym, or Update Base Synonyms.

    The synonym creation process for most adapters has been consolidated so that you can enter all necessary parameters on one page. However, for some adapters such as LDAP, continue clicking Next until you get to a page that has a Create Synonym button.

Result

The synonym is created and added under the specified application directory.

Note: When creating a synonym, if you select the Validate checkbox, where available, the server adjusts special characters and checks for reserved words. For more information, see Validation for Special Characters and Reserved Words.

Synonym Creation Parameters for Snowflake Cloud Data Warehouse (JDBC)

Object Type

Restrict candidates for synonym creation based on the selected object types: Tables, Views, External SQL Scripts, and any other supported objects.

Choosing External SQL Scripts from the drop-down list enables you to represent an SQL Query as a synonym for read-only reporting. A Synonym candidate can be any file that contains one (and only one) valid SQL Query and does not contain end-of-statement delimiters (";" or "/") and comments.

Depending on the adapter, you can further restrict your search by choosing checkboxes for listed objects.

Database

Specify a database from which you can select a table or other object. You can select a database from the drop-down list. To select the database that is specified as the Default Database in the connection parameters, select the Default Database option from the drop-down list.

Owner/Schema

Select a schema from the drop-down list or type a string for filtering the selection, inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter: ABC% to select tables or views whose owner/schema begin with the letters ABC; %ABC to select tables or views whose owner/schema end with the letters ABC; %ABC% to select tables or views whose owner/schema contain the letters ABC at the beginning, middle, or end.

Object Name

Type a string for filtering the object names, inserting the wildcard character (%) as needed at the beginning and/or end of the string. For example, enter: ABC% to select all objects whose names begin with the letters ABC; %ABC to select all whose names end with the letters ABC; %ABC% to select all whose names contain the letters ABC at the beginning, middle, or end.

To filter the object names, click the Filter icon after typing the string in the Object Name field.

Customize data type mappings

To change the data type mappings from their default settings, expand the Customize data type mappings section. The customizable mappings are displayed.

The Customize data type mappings are given below.

  • Integer Precision. Controls mapping of the Integer Precision columns. The default value is 11.
  • Float Precision. Controls mapping of the Float Precision columns. The default value is 20.
  • Float Scale. Controls mapping of the Float Scale columns. The default value is 2.
  • Longchar mapped as. Controls processing and mapping of large character data types. The Longchar mapping options are ALPHA, TEXT, or BLOB. The default value is ALPHA.
  • Longchar Length. This setting is available only when the value of the Longchar mapped as setting is set to ALPHA. The default value is 256.
  • Exact Datetime precision. Controls mapping of the DATETIME columns. This is a compatibility setting. When set to ON, the exact precision of date-time is preserved in the synonym field format. For example, TIMESTAMP(5) is mapped to HYYMD5 instead of HYYMDs, thus preserves all 5 fractional digits. The default value is OFF.
  • Decompose Date fields into components. When set to ON, decomposes Date fields and date portion of Date-Time fields into Year, Quarter, Month, and Day components. The default value is OFF.
  • Date Order. Controls mapping of the date format into Master File as YYMD, MDYY, and DMYY. The default value is adapter specific.
  • Activate GEOGRAPHIC_ROLE assignment. When set to ON, assigns GEOGRAPHIC_ROLE based on column name analysis according to the vocabulary information stored and roles in the configuration file. Create Synonym maps the GEOGRAPHIC_ROLE Master File attribute to the certain geometry (GEOMETRY_AREA or GEOMETRY_POINT) or geography (For example, CITY, STATE, and ZIP5) role defined in the server configuration file (edahome/catalog/geo_services.xml). The complete list of values can be obtained by running edahome/catalog/geo_srv_roles.fex. The default value is ON.
Application

Select an application directory. The default value is ibisamp.

Prefix/Suffix

If you have tables with identical table names, assign a prefix or a suffix to distinguish them. For example, if you have identically named human resources and payroll tables, assign the prefix HR to distinguish the synonyms for the human resources tables. Note that the resulting synonym name cannot exceed 64 characters.

If all tables and views have unique names, leave the prefix and suffix fields blank.

Row Limit

Select the number of objects to display on the Create Synonym page.

Choose Columns

Select the columns to display for the synonym candidates of the selected database.

Filters

Filter the selection from the synonym candidates list. To filter your selection from the synonym candidates list, type the string to filter and click the search icon. For example, type ABC to search all objects whose names have the letters ABC in them.