SnowConvert AI - Différences fonctionnelles Hive¶

SSC-FDM-HV0001¶

L’insertion de valeurs dans une table externe n’est pas prise en charge dans Snowflake.

Description¶

Les tables au format Hive vous permettent d’insérer des valeurs, mais les tables externes Snowflake ne prennent pas en charge les insertions de valeurs. Cela signifie que pendant la conversion de la structure de la table, toutes les opérations qui tentent d’insérer des données directement dans la table externe dans Snowflake échoueront.

Exemple de code¶

Entrée¶

Spark¶

 CREATE EXTERNAL TABLE IF NOT EXISTS External_table_hive_format
(
  order_id int,
  date string,
  client_name string,
  total float
)
stored as AVRO
LOCATION 'gs://sc_external_table_bucket/folder_with_avro/orders.avro';

Sortie¶

Snowflake¶

 --** SSC-FDM-HV0001 - INSERTING VALUES INTO AN EXTERNAL TABLE IS NOT SUPPORTED IN SNOWFLAKE **
CREATE EXTERNAL TABLE IF NOT EXISTS hive_format_orders_Andres
(
  order_id int AS CAST(GET_IGNORE_CASE($1, 'order_id') AS int),
  date string AS CAST(GET_IGNORE_CASE($1, 'date') AS string),
  client_name string AS CAST(GET_IGNORE_CASE($1, 'client_name') AS string),
  total float AS CAST(GET_IGNORE_CASE($1, 'total') AS float)
)
!!!RESOLVE EWI!!! /*** SSC-EWI-0032 - EXTERNAL TABLE REQUIRES AN EXTERNAL STAGE TO ACCESS gs:, DEFINE AND REPLACE THE EXTERNAL_STAGE PLACEHOLDER ***/!!!
LOCATION = @EXTERNAL_STAGE
AUTO_REFRESH = false
FILE_FORMAT = (TYPE = AVRO)
PATTERN = '/sc_external_table_bucket/folder_with_avro/orders.avro'
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "spark",  "convertedOn": "06/18/2025",  "domain": "no-domain-provided" }}';

Meilleures pratiques¶

Si vous avez besoin de plus d’assistance, vous pouvez nous envoyer un e-mail à snowconvert-support@snowflake.com.

SSC-FDM-HV0002¶

Partitioned column added to table definition

Description¶

For Hive/Spark partitioned tables, the partition columns are stored in the directory structure rather than in the table data. Snowflake does not support this pattern. SnowConvert AI adds the partitioned columns to the table definition as regular columns so the table schema is complete.

Exemple de code¶

Entrée¶

Hive¶

 CREATE EXTERNAL TABLE sales_data
(
  product_id INT,
  amount DECIMAL(10,2)
)
PARTITIONED BY (sale_month STRING)
STORED AS PARQUET
LOCATION 's3://bucket/sales/';

Sortie¶

Snowflake¶

 CREATE EXTERNAL TABLE sales_data (
  product_id INT,
  amount DECIMAL(10,2),
  sale_month STRING
)
--** SSC-FDM-HV0002 - PARTITIONED COLUMN ADDED TO TABLE DEFINITION. **
LOCATION = @EXTERNAL_STAGE
FILE_FORMAT = (TYPE = PARQUET);

Meilleures pratiques¶

Verify that partition columns are correctly mapped to your file path structure.
Si vous avez besoin de plus d’assistance, vous pouvez nous envoyer un e-mail à snowconvert-support@snowflake.com.

SSC-FDM-HV0003¶

NULL format parameter is not supported in FROM_UNIXTIME

Description¶

Hive’s FROM_UNIXTIME function allows a NULL format parameter, in which case it uses a default format. Snowflake’s equivalent (TO_VARCHAR with TO_TIMESTAMP_NTZ) does not support a NULL format parameter. SnowConvert AI passes the NULL through, but the conversion may fail at runtime or behave unexpectedly.

Exemple de code¶

Entrée¶

Hive¶

 SELECT FROM_UNIXTIME(1697328000, CAST(NULL AS STRING));

Sortie¶

Snowflake¶

 SELECT
  --** SSC-FDM-HV0003 - NULL FORMAT PARAMETER IS NOT SUPPORTED IN FROM_UNIXTIME. **
  TO_VARCHAR(TO_TIMESTAMP_NTZ(1697328000), CAST(NULL AS STRING));

Meilleures pratiques¶

Replace NULL format parameters with an explicit format string (e.g., “yyyy-MM-dd HH:mm:ss”).
Si vous avez besoin de plus d’assistance, vous pouvez nous envoyer un e-mail à snowconvert-support@snowflake.com.

SSC-FDM-HV0004¶

INSTR transformed to REGEXP_INSTR changes literal to regex pattern

Description¶

Hive’s INSTR function uses literal string matching. Snowflake does not have INSTR; SnowConvert AI translates it to REGEXP_INSTR. REGEXP_INSTR interprets the pattern as a regex, so metacharacters (e.g., ., *, $) will behave differently than in Hive’s literal matching.

Exemple de code¶

Entrée¶

Hive¶

 SELECT INSTR('price: $10.99', pattern_col, 1, 1);

Sortie¶

Snowflake¶

 SELECT
  --** SSC-FDM-HV0004 - HIVE'S INSTR USES LITERAL STRING MATCHING, BUT REGEXP_INSTR INTERPRETS THE PATTERN AS A REGEX. METACHARACTERS WILL BEHAVE DIFFERENTLY. **
  REGEXP_INSTR('price: $10.99', pattern_col, 1, 1);

Meilleures pratiques¶

When the pattern contains regex metacharacters, escape them or use REGEXP_REPLACE to sanitize the pattern.
Si vous avez besoin de plus d’assistance, vous pouvez nous envoyer un e-mail à snowconvert-support@snowflake.com.