BYTES

Bytes data type and usages

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

Sequence of bytes with a maximum of L bytes allowed in the binary string. The maximum length is 8 MB (8,388,608 bytes). For more information please refer to BigQuery BYTES data type.

BYTES data type is not supported in Snowflake, currently transformed to BINARY.

Sample Source Patterns

BYTES output format

The default output format for binary data types in BigQuery is 'BASE64' and in Snowflake 'HEX'. For this reason, when a binary column is selected, the BASE64_ENCODE function is automatically added. In order to maintain the default formatting of BigQuery.

Input Code:

BigQuery

IN -> BigQuery_01.sql

CREATE OR REPLACE TABLE bytesTable
(
  COL1 BYTES,
  COL2 BYTES(20)
);

INSERT INTO bytesTable VALUES (B"01020304", B"""AABBCCDD""");
INSERT INTO bytesTable VALUES (B'''\x01\x02\x03''', B"/+A=");

SELECT COL1 FROM bytesTable;

Output Code:

Snowflake:

OUT -> BigQuery_01.sql

CREATE OR REPLACE TABLE bytesTable
(
  COL1 BINARY,
  COL2 BINARY(20)
);

INSERT INTO bytesTable
SELECT
  TRY_TO_BINARY('01020304', 'utf-8'),
  TRY_TO_BINARY('AABBCCDD', 'utf-8');
  
INSERT INTO bytesTable
SELECT
  TRY_TO_BINARY('\x01\x02\x03', 'utf-8'),
  TRY_TO_BINARY('/+A=', 'utf-8');
  
SELECT BASE64_ENCODE( COL1) FROM bytesTable;

In case it is not added automatically and you want to see the data in BASE64 format, you can use the BASE64_ENCODE function or set the BINARY_OUTPUT_FORMAT format.

BYTES Literal

The following cases represent the forms that can be used to format byte literals in BigQuery.

B"abc"
B'''abc'''
b"""abc"""

These literals are not supported in Snowflake, but instead the TRY_TO_BINARY function can be used to convert the input expression to a binary value. This function is a special version of TO_BINARY that performs the same operation, but with error handling support.

It is important to take into consideration that the binary format for the conversion can be: HEX, BASE64, or UTF-8. The default is the value of the BINARY_INPUT_FORMAT session parameter. If this parameter is not set, the default value is HEX.

Observations

• Please keep in mind that the default output format for binary data types in BigQuery is 'BASE64' and in Snowflake 'HEX'. You can use the BASE64_ENCODE function or set the BINARY_OUTPUT_FORMAT format if you want to view the data in BASE64 format.

• The only formats supported by Snowflake are: HEX, BASE64, or UTF-8. For more information, please refer to Binary Input and Output in Snowflake.

• Binary functions used to insert data into a values clause are not supported in Snowflake. Please refer to Bytes Functions documentation to learn more about this.