This page was moved to https://api-docs.treasuredata.com/en/tools/hive/hive_troubleshooting/#invalid-table-alias-or-column-reference

Problem

Job failed with the following error message

FAILED: SemanticException [Error 10004]: Line 3:29 Invalid table alias or column reference 'order_id': (possible column names are: _c0, _c1)

Failed Query:

SELECT
TD_DATE_TRUNC(
'day',
CAST(
CONV(SUBSTR(order_id,
0,
8),
16,
10) AS BIGINT
),
'America/Los_Angeles'
),
COUNT(*)
FROM
pj_db.pj_table
GROUP BY
1
ORDER BY
1

Cause

The issue caused by the Hive ORDER BY with a column number. Table aliases in order by clause lead to semantic analysis failure

Solution

  1. Use column alias in order by instead of column number

Working Query:

SELECT
TD_DATE_TRUNC(
'day',
CAST(
CONV(SUBSTR(order_id,
0,
8),
16,
10) AS BIGINT
),
'America/Los_Angeles'
),
COUNT(*)
FROM
pj_db.pj_table
GROUP BY
1
ORDER BY
ORDER_ID

Copyright © 2021 Treasure Data, Inc. (or its affiliates). All rights reserved.