BYTES

Data Type and usages

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.

BigQuery

Query

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 test.bytesType;

Output

MDEwMjAzMDQ=	
AQID

Snowflake

Query

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 test.bytesType;

Output

MDEwMjAzMDQ=
AQID

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.

Sample Source

BigQuery

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

Snowflake

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

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.