Migrating from Text to Binary Storage Formats - Advanced SQL Engine - Teradata Database

JSON Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
gzn1554761068186.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1150
lifecycle
previous
Product Category
Teradata Vantageā„¢
You cannot use the ALTER TABLE statement to migrate JSON data from the text format to one of the binary formats. In order to perform this conversion, you should do the following:
  1. Create a new table with an identical structure as the source table and which has JSON column(s) defined with the binary storage format.
  2. Use INSERT...SELECT to populate the new table. This will implicitly cast the JSON text data to the binary format.

Note that the space needed to store data in one storage format is not guaranteed to be equal to the space needed to store the identical data in a different storage format. For example, data stored as text may be smaller than data stored as BSON, or vice versa. Therefore, it is necessary to determine how much space is required to store the data in the new format. You can use the StorageSize method to perform this analysis before creating the new table.

For example, suppose you have an existing table like the following:

CREATE TABLE jsonTextTable(id INTEGER, j JSON(1000) CHARACTER SET LATIN);

/*load many rows of data*/

If you want to migrate the JSON data from being stored as text to being stored as BSON, you must determine the maximum size needed using a query like the following:

SELECT MAX(j.StorageSize('BSON')) FROM jsonTextTable;

The result of this query can then be used to create the new table. In this example, suppose the result was X.

CREATE TABLE jsonBSONTable(id INTEGER, j JSON(X) STORAGE FORMAT BSON);
INSERT INTO jsonBSONTable SELECT * FROM jsonTextTable;