Requirements for using semi-structured data in Snowflake

To define semi-structured data type columns in Snowflake and input or output the data in DataMagic, certain conditions must be met.

Before you create the database table information and the data processing information, check if the following conditions are met:

 

Requirements for using VARIANT type

• Specification method is set to SQL statement specification on the Database Table - Detailed Information screen. (You cannot use the BULK INSERT update method, because you cannot specify Simple for Specification method.)

• For a SQL statement whose I/O category is Input, the statement meets the following conditions:

  • The data types of the columns in the actual data are numeric type, string type, binary type, logical data type, or date and time type.

  • For all the columns that are targets of SELECT, the data types are converted with the CAST function and the aliases are set with the AS clause. (You cannot use "SELECT *".)

  • For all the columns, the actual data types match the data types to be converted to via CAST.

• For a SQL statement whose I/O category is Output, the statement meets the following conditions:

  • The types of the columns in the actual data are numeric type, string type, binary type, or date and time type.

  • For all the columns that are targets of INSERT, the data types are converted to the VARIANT type with the CAST function.

  • The values in Fixed/Variable length, Size, and Field type that can be obtained from Import Fields on the Database Table - Detailed Information screen have been manually reset. (This is because the obtained information is tentative. Import Fields obtains VARIANT type values as VARCHAR type values that are variable length and have a size of zero.