Spectrum中含点列名（如account_id.cust_id）的创建与查询求助

Got it, let's break down why you're hitting this error and how to fix it. The core problem is that SQL engines treat dots as a separator between objects (like schema.table.column) by default. When you write account_id.cust_id, the engine thinks you're trying to reference the cust_id column from a table/schema named account_id inside your customers table—which doesn't exist, hence the "column does not exist" error.

Here's how to properly handle these dot-containing column names when creating external tables and writing queries:

1. Creating the External Table #

The key is to wrap the column name in the correct identifier quote for your SQL engine. Different systems use different syntax:

For Hive/Spark SQL #

Use backticks (`) to wrap the entire column name. This tells the engine to treat the dot as part of the column identifier, not a separator. Example:

CREATE EXTERNAL TABLE customers (
  `account_id.cust_id` string,
  `account_id.ord_id` string,
  -- add other columns here
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LOCATION '/path/to/external/data';

For ANSI-SQL Compatible Engines (Redshift, PostgreSQL, BigQuery) #

Use double quotes (") to wrap the column name. These engines follow ANSI standards where double quotes denote quoted identifiers. Example:

CREATE EXTERNAL TABLE customers (
  "account_id.cust_id" varchar(255),
  "account_id.ord_id" varchar(255),
  -- add other columns here
)
S3_LOCATION 's3://your-bucket/data-path'
FORMAT AS PARQUET;

2. Querying the Table #

When selecting these columns, you need to use the same wrapping syntax as you did when creating the table:

Hive/Spark SQL Example #

SELECT `account_id.cust_id`, `account_id.ord_id`
FROM customers
WHERE `account_id.cust_id` IS NOT NULL;

Redshift/PostgreSQL Example #

SELECT "account_id.cust_id", "account_id.ord_id"
FROM customers
WHERE "account_id.cust_id" <> '';

Key Mistakes to Avoid #

• Don't use single quotes: Single quotes are for string literals, not column identifiers. Writing 'account_id.cust_id' would be treated as a static string, not a column reference.
• Match your wrapping syntax: If you created the table with backticks, use backticks in queries. If you used double quotes, stick with double quotes—mixing them will cause errors.

内容的提问来源于stack exchange，提问作者Prajakta Yerpude