AWS Big Data Blog

Store and process large strings in Amazon Redshift SUPER columns

Storing large semi-structured data such as JSON and arrays in a data warehouse often means splitting or truncating your data. Amazon Redshift removes this with the SUPER data type and PartiQL language so you can ingest, store, transform, and analyze semi-structured data such as weblogs and sensor data at scale. When you turn on large string support, SUPER supports individual strings up to 16 million bytes and processes strings beyond the 65,535 bytes VARCHAR limit in memory. SUPER also works in case-insensitive databases.

In this post, we walk you through three key SUPER data type enhancements that simplify semi-structured data workflows and reduce common migration challenges.

  • Handling large SUPER objects with extended string support: Learn how the 16 million bytes improvement helps you work with large JSON documents that previously exceeded size limits
  • In-memory string function processing without truncation for improved efficiency
  • Migration from your data warehouse with case insensitivity support: Explore how case-insensitive database compatibility simplifies migrations from legacy databases

Prerequisites

To implement this solution, you need the following prerequisites:

Use case overview

The SUPER data type helps you process and query semi-structured or nested data. This includes web logs, industrial Internet of Things (IoT) data from machines and equipment, sensor data, genomics data, and more.

The following diagram illustrates the data flow. It shows how data is generated, collected, and stored in Amazon Redshift as the SUPER data type:

Figure 1: Use case overview flow

Use case one: Handling large JSON documents with extended string support

The challenge

Today, organizations need to store and analyze large semi-structured documents such as detailed customer profiles, comprehensive transaction records, IoT sensor data, and complex event logs.

The solution

Amazon Redshift now supports storing objects up to 16 million bytes in SUPER data type columns. When you turn on large string support, individual strings within SUPER columns now support up to 16 million bytes. You can store complete legal documents, detailed product catalogs with formatting intact, full application logs, extensive customer interaction histories, and entire articles, with no splitting or truncating your data.

To follow along with this solution, complete the following:

Turn on large string support. Create a parameter group (or edit an existing one), set enable_large_strings_opt_in to Yes, and attach the parameter group to your data warehouse. For a provisioned cluster, reboot the cluster to apply the change. For Amazon Redshift Serverless, set the enable_large_strings_opt_in configuration parameter on your workgroup instead, because Serverless doesn’t use parameter groups.

Confirm the setting:

SELECT is_large_string_enabled();

Create the orders table:

CREATE TABLE orders ( data SUPER);

Load large JSON documents (up to 16 million bytes) with strings > 65535 bytes into orders table using COPY:

COPY orders
FROM 's3://<your-bucket>/orders.json'
IAM_ROLE 'arn:aws:iam::1234567890:role/service-role/<Redshift-copy-unload-role>'
FORMAT JSON 'noshred';

Select data from orders table:

SELECT * FROM orders;

Figure 2: Orders result set

The following query extracts specific fields from nested JSON documents stored in the data SUPER column of the orders table. Before querying, you set enable_case_sensitive_super_attribute to true, so you can reference camelCase attribute names such as orderId and shippingAddress without double quotation marks. The SELECT clause retrieves order and customer identifiers, shipping address details, and a description field that supports up to 16 million bytes of text, along with its character length. The WHERE clause returns only orders with a status of ‘processing‘.

-- Set case sensitivity
SET enable_case_sensitive_super_attribute TO true;

-- Query nested order data with large string values
SELECT
    data.orderId::VARCHAR                     AS order_id,
    data.customerId"::VARCHAR                  AS customer_id,
    data.shippingAddress.state::VARCHAR     AS shipping_state,
    data.shippingAddress.zipCode::VARCHAR   AS zip_code,
    data.detailedDescription::VARCHAR         AS detailed_description,
    LENGTH(data."detailedDescription"::VARCHAR) AS description_length
FROM orders
WHERE data.status::VARCHAR = 'processing';

Figure 3: Large string result set

Use case two: In-memory string function operations without truncation

The challenge

Running string operations on large semi-structured data has trade-offs such as either truncating at 65,535 bytes and losing critical information, or disk spills impacting query performance. Standard SQL string functions like substring extraction, pattern matching, and text transformations become impractical when your data exceeds these limits. This leaves you with incomplete analysis or unacceptable latency.

The solution

When you set enable_large_strings_opt_in to Yes, Amazon Redshift supports in-memory string function operations on SUPER data that exceed the 65,535 bytes VARCHAR limit. By casting SUPER values to VARCHAR within string functions, Amazon Redshift processes the full string in memory without truncation to upper limit of 16 million bytes. Table columns defined as VARCHAR(MAX) stay at 65,535 bytes. The larger limit applies to in-memory operations such as CAST expressions, views, UDFs, and stored procedures.

How it works:

  • Without CAST: String functions directly on SUPER columns can cause errors
  • With CAST: Cast SUPER values to VARCHAR first, then apply string functions without truncation or errors

Problem scenario (causes error):

Run the following query to retrieve customer IDs from the orders table and convert them to lowercase; you will get an error.

SELECT
lower(data.customerId) AS customer_id
FROM orders;

Figure 4: Output without using CAST

Solution (works without truncation):

Run the following query to retrieve customer IDs from the orders table, cast them to VARCHAR and convert them to lowercase, query executes successfully.

SELECT
lower(data.customerId::VARCHAR) AS customer_id
FROM orders;

Figure 5: Query result showing customer IDs

Next, let’s see how the SUPER data type accommodates string values larger than 65,535 bytes. We use the LISTAGG and LEN functions to test this enhancement. The LISTAGG window function orders rows for each group according to the ORDER BY expression, then concatenates the values into a single string. The LEN function returns the length of the specified string as the number of characters. This operation wasn’t possible with the previous 65,535 bytes limit.

SELECT
LEN(LISTAGG(data.detailedDescription::VARCHAR, ', ')WITHIN GROUP (ORDER BY data.orderId::VARCHAR)) AS description_length
FROM orders
WHERE data.status::VARCHAR = 'processing';

Figure 6: Query result showing description length

The output shows a string length of 1,212,008 characters. This demonstrates that SUPER can now handle strings beyond the previous 65,535-byte limit. If the output of LISTAGG exceeds the 65,535-byte limit of a VARCHAR column, you can store the result in a SUPER column, which supports up to 16 million bytes.

With large string support turned on, VARCHAR(MAX) in a view resolves to VARCHAR(16000000), so the view can return full-length strings. Because views keep the case settings from their creation, run SET first, then create the view after you turn on the setting:

SET enable_case_sensitive_super_attribute TO true;
CREATE VIEW order_descriptions AS
SELECT     
   data.orderId::VARCHAR AS order_id, 
   data.detailedDescription::VARCHAR(MAX) AS detailed_description
FROM orders;

Views that you created before you turned on the setting stay capped at 65,535 bytes until you recreate them. For more information, see Behavior changes in Amazon Redshift.

Use case three: Migration from your data warehouse with case insensitivity support

The challenge

Organizations migrating from legacy data warehouses to Amazon Redshift face a critical decision regarding database collation. Your data warehouse operates with case-insensitive string comparisons by default, and maintaining this behavior is essential for application compatibility. However, until recently, SUPER data types were only supported in Amazon Redshift databases with case-sensitive collation, creating a migration barrier. If you created Amazon Redshift case-insensitive databases to ease your migration process (avoiding the extensive testing required for case-sensitive conversion), you couldn’t use the SUPER data type for ingesting and querying semi-structured data such as JSON and arrays in Amazon Redshift native and data lake tables.

The solution

Amazon Redshift announced support for SUPER data types in databases with case-insensitive collation, which has the following benefits:

  • Maintain case-insensitive behavior for legacy applications
  • Use SUPER columns for semi-structured data in the same database
  • Use the COLLATE function to specify case sensitivity preferences for SUPER columns

Amazon Redshift native table with SUPER data type

  1. Log in to Amazon Redshift Query Editor V2. Create database with case insensitive collation.
CREATE DATABASE custdb_case_insensitive COLLATE CASE_INSENSITIVE;
  1. Create the customer_data table with SUPER data type columns and insert sample records.
CREATE TABLE customer_data (
    customer_id    INTEGER,
    customer_name  VARCHAR(100),
    customer_profile SUPER,
    order_history  SUPER COLLATE case_sensitive,
    metadata       SUPER,
    created_date   TIMESTAMP DEFAULT GETDATE());

INSERT INTO customer_data (customer_id, customer_name, customer_profile, order_history, metadata)
VALUES (
    1,
    'John Doe',
    JSON_PARSE('{"age": 30, "email": "john@example.com", "address": {"street": "123 Main St", "city": "New York", "zipcode": "10001"}}'),
    JSON_PARSE('[{"order_id": 101, "amount": 250.50, "items": ["laptop", "mouse"]}, {"order_id": 102, "amount": 89.99, "items": ["keyboard"]}]'),
    JSON_PARSE('{"source": "web", "campaign": "spring_sale", "tags": ["premium", "verified"]}')
);
INSERT INTO customer_data (customer_id, customer_name, customer_profile, order_history, metadata)
VALUES (
    2,
    'Alice Smith',
    JSON_PARSE ('{
        "age": 35,
        "email": "alice@example.com",
        "preferences": {
            "newsletter": true,
            "notifications": ["email", "sms"],
            "language": "en"
        },
        "address": {
            "street": "321 Elm St",
            "city": "Boston",
            "state": "MA",
            "zipcode": "01234"
        }
    }'),
    JSON_PARSE ('[
        {
            "order_id": 106,
            "amount": 599.99,
            "date": "2024-01-15",
            "items": [
                {"name": "smartphone", "price": 499.99, "quantity": 1},
                {"name": "screen_protector", "price": 19.99, "quantity": 2}
            ],
            "status": "delivered"
        }
    ]'),
    JSON_PARSE ('{"source": "web", "device": "desktop", "browser": "Chrome"}')
);
  1. Query customer_data table to analyze super data type columns.

SET enable_case_sensitive_super_attribute TO true;
SELECT * FROM customer_data;


Figure 7: Result set of customer_data

  1. Use the following two queries to unnest the order_history array column using PartiQL.
SELECT
customer_id,
customer_name,
customer_profile.age,
customer_profile.email,
customer_profile.address.city,
order_history[0].order_id AS first_order_id,
order_history[0].amount AS first_order_amount,
order_history[0].items[0] AS first_item
FROM customer_data c;

Figure 8: Result set of customer_data using partiql

SELECT
customer_id,
customer_name,
customer_profile.age,
customer_profile.email,
customer_profile.address.city,
orders.order_id,
orders.amount
FROM customer_data c, c.order_history AS orders;

Figure 9: Result set of customer_data and order_history using partiql

  1. Run a query that depends on the case-insensitive collation of the database. The filter uses lowercase “new york” and still matches “New York”. 
    -- Matches 'New York' because the database is case insensitive 
SELECT 
customer_id, 
customer_profile.address.city 
FROM customer_data 
WHERE customer_profile.address.city::VARCHAR = 'new york';

In this example, the order_history column uses COLLATE case_sensitive, which overrides the case-insensitive default of the database for that column. To compare both behaviors, you can also use the COLLATE function in a WHERE clause.

Query nested data type on data lake

  1. The following is the sample input data for the customer profile. Place this data into a file and upload it to Amazon S3, name the S3 prefix “customer_profile”.
{
  "customer_id": 1001,
  "name": "John Smith",
  "email": "john.smith@email.com",
  "registration_date": "2023-01-15",
  "profile": {
    "age": 35,
    "gender": "M",
    "occupation": "Software Engineer",
    "income_range": "75000-100000",
    "preferences": {
      "communication": ["email", "sms"],
      "categories": ["electronics", "books", "sports"]
    },
    "address": {
      "street": "123 Main St",
      "city": "Seattle",
      "state": "WA",
      "zip": "98101",
      "country": "USA"
    },
    "loyalty_tier": "gold",
    "interests": ["technology", "hiking", "photography"]
  }
}
  1. Create the AWS Glue database datalake_withsuper_datatype. For instructions on how to create the AWS Glue database, see Creating databases.
  2. Create the AWS Glue crawler to crawl customer_profile prefix, use AWS Glue database datalake_withsuper_datatype in crawler. For instructions on how to create a crawler, see Tutorial: Adding an AWS Glue crawler.
  3. Create external schema on Amazon Redshift in database custdb_case_insensitive.
CREATE EXTERNAL SCHEMA IF NOT EXISTS datalake_cust_profile
FROM DATA CATALOG
DATABASE 'datalake_withsuper_datatype'
IAM_ROLE default
CREATE EXTERNAL DATABASE IF NOT EXISTS;
  1. Select the external table customer_profile to view the column profile, which is the struct column in the AWS Glue table.
SELECT * FROM
"custdb_case_insensitive"."datalake_cust_profile"."customer_profile";


Figure 10: Result set of customer_profile using glue data catlog

Best practices

In this section, we discuss the best practices that you can follow while using SUPER data type in Amazon Redshift.

  1. Configure case sensitivity settings

Set the following session setting before you query SUPER columns for accurate field name matching. With this setting on, reference field names without double quotation marks:

SET enable_case_sensitive_super_attribute TO true;

Without this setting, Amazon Redshift converts unquoted field names to lowercase. When your JSON contains orderId but Amazon Redshift searches for orderid, queries return NULL values instead of your data.

Example: Impact of enabling case sensitivity

The following example shows the difference in query results with and without case sensitivity enabled:

-- Without the setting - returns NULL
SELECT data.orderId::VARCHAR FROM orders;
-- With the setting - returns actual values
SET enable_case_sensitive_super_attribute TO true;
SELECT data.orderId::VARCHAR FROM orders;
  1. Cast SUPER values to specific data types

Always cast SUPER values to appropriate data types for better performance and accuracy:

SELECT
    data.orderId::VARCHAR          AS order_id,
    data.totalAmount::DECIMAL(10,2) AS total_amount,
    data.shippingAddress."zipCode"::VARCHAR AS zip_code,
    data.items."quantity"::INTEGER AS first_item_qty
FROM orders
WHERE data.status::VARCHAR = 'processing';

Explicit casting eliminates ambiguity and improves query performance by helping Amazon Redshift optimize execution plans.

  1. Optimize performance for production workloads

Promote frequently queried fields

Extract commonly accessed SUPER attributes to regular columns for faster queries:

ALTER TABLE orders ADD COLUMN customer_id VARCHAR(50);
UPDATE orders SET customer_id = data.customerId::VARCHAR;

Create materialized views for complex queries

Turn on enable_case_sensitive_super_attribute, then build materialized views for queries you run repeatedly:

SET enable_case_sensitive_super_attribute TO true;
CREATE MATERIALIZED VIEW order_summary AS
SELECT
    data.orderId::VARCHAR           AS order_id,
    data.customerId::VARCHAR        AS customer_id,
    data.totalAmount::DECIMAL(10,2) AS total_amount,
    LENGTH(data.detailedDescription::VARCHAR) AS description_length
FROM orders;
REFRESH MATERIALIZED VIEW order_summary;
  1. Migrate from legacy databases

Use AWS Schema Conversion Tool (AWS SCT) to convert your schema automatically, then configure case sensitivity for SUPER columns.

Set user-level configuration

Enable case sensitivity for all of a single user’s sessions:

ALTER USER myuser SET enable_case_sensitive_super_attribute TO true;

Test both column types

Verify that regular columns maintain case-insensitive behavior:

-- Regular columns remain case-insensitive
SELECT COUNT(*) FROM customer_data WHERE cust_name = 'JOHN DOE';
SELECT COUNT(*) FROM customer_data WHERE cust_name = 'john doe';
-- Both return identical results

Confirm that SUPER columns enforce case sensitivity:

SET enable_case_sensitive_super_attribute TO true;
-- Correct - matches JSON field exactly
SELECT customer_profile.address.city::VARCHAR FROM customer_data;
-- Incorrect - returns NULL due to case mismatch
SELECT customer_profile.Address.City::VARCHAR FROM customer_data;

Update application queries:

Modify your application code to use correct case and explicit casting for all SUPER column attributes. This ensures consistent results and prevents NULL values from case mismatches.

  1. Align the large string setting

Keep the enable_large_strings_opt_in setting the same on data sharing producer and consumer data warehouses.

Recreate existing views, UDFs, and stored procedures after you turn on the setting. They stay capped at 65,535 bytes until you recreate them.

After you set the parameter to Yes or No, you can’t reset it to the default value.
For more information about best practices for working with semi-structured data types, see documentation.

Conclusion

The enhanced SUPER data type of Amazon Redshift solves the previously mentioned critical challenges that you face when working with semi-structured data. This post showed you how to turn on large string support, store individual strings in JSON documents up to 16 million bytes, perform string operations without truncation errors, and maintain case-insensitive compatibility during database migrations.

With SUPER, you can handle comprehensive data objects including legal contracts, product catalogs, customer profiles, and IoT sensor readings without breaking them into smaller pieces.

You can also cast SUPER values to VARCHAR and run pattern matching, text manipulation, and data cleansing operations directly on SUPER columns, and remove the truncation errors that previously blocked your analysis workflows.

Start implementing these capabilities in your environment with the following steps:

  1. Turn on large string support
  2. Configure case sensitivity settings for your specific use case
  3. Cast data types explicitly during data extraction
  4. Create materialized views for complex queries
  5. Test thoroughly to optimize query performance

Learn more

About the authors

Ritesh Kumar Sinha

Ritesh Ritesh Kumar Sinha is an Senior Analytics Specialist Solutions Architect based out of San Francisco. He has helped customers build scalable data warehousing and big data solutions for over 16 years. He loves to design and build efficient end-to-end solutions on AWS. In his spare time, he loves reading, walking, and doing yoga.

Raghu Kuppala

Raghu is an Analytics Specialist Solutions Architect experienced working in the databases, data warehousing, and analytics space. Outside of work, he enjoys trying different cuisines and spending time with his family and friends.

Raza Hafeez

Raza is a Senior Product Manager at Amazon Redshift. He has over 13 years of professional experience building and optimizing enterprise data warehouses and is passionate about enabling customers to realize the power of their data. He specializes in migrating enterprise data warehouses to AWS Modern Data Architecture.

Harshida Patel

Harshida Patel

Harshida is a Specialist Sr. Solutions Architect for Analytics with AWS.