SnowConvert AI – Diferenças funcionais do Hive

SSC-FDM-HV0001

A inserção de valores em uma tabela externa não é compatível com o Snowflake.

Descrição

As tabelas de formato Hive permitem a inserção de valores, mas as tabelas externas do Snowflake não são compatíveis com inserções de valores. Isso significa que, embora a estrutura da tabela seja convertida, qualquer operação que tente inserir dados diretamente na tabela externa no Snowflake falhará.

Exemplo de código

Entrada

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';

Saída

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" }}';

Práticas recomendadas

SSC-FDM-HV0002

Partitioned column added to table definition

Descrição

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.

Exemplo de código

Entrada

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/';

Saída

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);

Práticas recomendadas

  • Verify that partition columns are correctly mapped to your file path structure.

  • Se precisar de mais suporte, envie um e-mail para snowconvert-support@snowflake.com

SSC-FDM-HV0003

NULL format parameter is not supported in FROM_UNIXTIME

Descrição

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.

Exemplo de código

Entrada

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

Saída

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

Práticas recomendadas

  • Replace NULL format parameters with an explicit format string (e.g., “yyyy-MM-dd HH:mm:ss”).

  • Se precisar de mais suporte, envie um e-mail para snowconvert-support@snowflake.com

SSC-FDM-HV0004

INSTR transformed to REGEXP_INSTR changes literal to regex pattern

Descrição

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.

Exemplo de código

Entrada

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

Saída

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);

Práticas recomendadas

  • When the pattern contains regex metacharacters, escape them or use REGEXP_REPLACE to sanitize the pattern.

  • Se precisar de mais suporte, envie um e-mail para snowconvert-support@snowflake.com