Forces the data type during data movement for a particular column to be one of the Cirro data types or a data type native to a specific database in the federation. This hint overrides the Cirro optimizer's choice of data type in data movements.

Syntax

/*+ FORCE_DATATYPE( dataTypeDef ) */

Arguments

  • dataTypeDef: A data type definition which will be applied to the designated column. This can be a Cirro data type or a type native to the system executing the query. Specify a native data type only when the data movement destination is known. Otherwise, specify a Cirro data type. When specifying a native data type, enclose in single quotes.

Hint Placement

SELECT
    column_expression [ /*+ FORCE_DATATYPE ( dataTypeDef )  */]
    [ ,column_expression [ /*+ FORCE_DATATYPE ( dataTypeDef ) */ ]
    ...n]
FROM
    fromClause;

Additional Information

  • This hint does not apply any kind of explicit casting to the column data, although implicit casting is possible when the underlying database platform supports it.

    • Implicit casting could occur, for example, when a VARCHAR(10) being moved from another data source in the federation is inserted into a database column field where the user has forced the column’s data type to be a VARCHAR(100). In most databases, this is an acceptable implicit conversion.
  • This hint is applied only to the data types in temporary tables created during data movement. If this hint is included in a query that does not require data movement, it will be ignored.

  • This hint is often used in combination with functions that cast/convert or otherwise transform the data prior to it being moved to another system.

  • Define a forced data type for a specific column only once within a query; if multiple and conflicting data type definitions are provided for a single column, the query engine will determine which data type to use.

Examples

Simple SELECT example

SELECT
    c1.customer_id,
    c1.first_name,
    c1.last_name /*+ FORCE_DATATYPE( varchar(100) ) */,
    c2.marital_status
FROM
    MYSQL1.inventory.inventory.customer c1
    INNER JOIN ORACLE1.ORACLE.CIRRO.cust c2
    ON c1.customer_id = c2.customer_id;

Multiple column example with a subquery

SELECT
c3.customer_id,
   c3.first_name,
   c3.last_name /*+ FORCE_DATATYPE( VARCHAR(100) ) */,
   c3.marital_status /*+ FORCE_DATATYPE( 'CHAR(1)' ) */,
   v.visit_date,
   v.visit_url
FROM
   (SELECT c1.customer_id, c1.first_name,
   c1.last_name /*+ FORCE_DATATYPE( 'VARCHAR2(100)' ) */,
   c2.marital_status
   FROM MYSQL1.inventory.inventory.customer c1
   INNER JOIN ORACLE1.ORACLE.CIRRO.cust c2
   ON c1.customer_id = c2.customer_id c3
   INNER JOIN HIVE1."default"."default".visits v
   ON c3.customer_id = v.customer_id