FAQ
Hi all,

I have deployed a pseudo cluster on my CentOS 6.3 64bit, then I want to
launch a tpc-h benchmark on it. After successfully load data, create table
and TPCH-HIVE.
when I executed tpc-h-impala some error happens
on q1_pricing_summary_report.
error log is here:
**********************************************************
[fty@impala-server tpch_impala]$ impala-shell -i impala-server -f
'q1_pricing_summary_report.impala'
Connected to impala-server:21000
Query: insert OVERWRITE TABLE q1_pricing_summary_report
SELECT
L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE),
SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)),
SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY),
AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1)
FROM
lineitem
WHERE
L_SHIPDATE<='1998-09-02'
GROUP BY L_RETURNFLAG, L_LINESTATUS
ORDER BY L_RETURNFLAG, L_LINESTATUS
LIMIT 2147483647
ERROR: com.cloudera.impala.common.AnalysisException: Analysis exception (in
insert OVERWRITE TABLE q1_pricing_summary_report
SELECT
L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE),
SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)),
SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY),
AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1)
FROM
lineitem
WHERE
L_SHIPDATE<='1998-09-02'
GROUP BY L_RETURNFLAG, L_LINESTATUS
ORDER BY L_RETURNFLAG, L_LINESTATUS
LIMIT 2147483647)
at
com.cloudera.impala.analysis.AnalysisContext.analyze(AnalysisContext.java:133)
at com.cloudera.impala.service.Frontend.createExecRequest(Frontend.java:221)
at
com.cloudera.impala.service.JniFrontend.createExecRequest(JniFrontend.java:91)
Caused by: com.cloudera.impala.common.AnalysisException: Inserting into
target table 'q1_pricing_summary_report' may result in loss of precision.
Would need to cast '<slot 17>' to 'INT'.
at
com.cloudera.impala.analysis.InsertStmt.checkTypeCompatibility(InsertStmt.java:314)
at
com.cloudera.impala.analysis.InsertStmt.checkUnionCompatibility(InsertStmt.java:273)
at com.cloudera.impala.analysis.InsertStmt.analyze(InsertStmt.java:124)
at
com.cloudera.impala.analysis.AnalysisContext.analyze(AnalysisContext.java:130)
... 2 more

Could not execute command: insert OVERWRITE TABLE q1_pricing_summary_report
SELECT
L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE),
SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)),
SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY),
AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1)
FROM
lineitem
WHERE
L_SHIPDATE<='1998-09-02'
GROUP BY L_RETURNFLAG, L_LINESTATUS
ORDER BY L_RETURNFLAG, L_LINESTATUS
LIMIT 2147483647
**********************************************************

q1_pricing_summary_report file is here:
**************************************************************
INSERT OVERWRITE TABLE q1_pricing_summary_report
SELECT
L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE),
SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)),
SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY),
AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1)
FROM
lineitem
WHERE
L_SHIPDATE<='1998-09-02'
GROUP BY L_RETURNFLAG, L_LINESTATUS
ORDER BY L_RETURNFLAG, L_LINESTATUS
LIMIT 2147483647;
**************************************************************

TABLE q1_pricing_summary_report is here
*******************************************************************
-- create the target table
CREATE TABLE q1_pricing_summary_report ( L_RETURNFLAG STRING, L_LINESTATUS
STRING, SUM_QTY DOUBLE, SUM_BASE_PRICE DOUBLE, SUM_DISC_PRICE DOUBLE,
SUM_CHARGE DOUBLE, AVE_QTY DOUBLE, AVE_PRICE DOUBLE, AVE_DISC DOUBLE,
COUNT_ORDER INT);
*******************************************************************

I know there is something wrong with the type when INSERT, but from these
files about the table and query I cannot find why this error happens.
who can help me for this problem! Thanks a lot !!!

Tianyuan

Search Discussions

  • Marcel Kornacker at Mar 14, 2013 at 2:16 pm
    The problem is that in your Insert statement you're doing an implicit
    conversion from BIGINT to INT, which is illegal because it loses
    precision. You can get around that by doing an explicit CAST, as in
    On Thu, Mar 14, 2013 at 2:23 AM, FU Tianyuan wrote:
    Hi all,

    I have deployed a pseudo cluster on my CentOS 6.3 64bit, then I want to
    launch a tpc-h benchmark on it. After successfully load data, create table
    and TPCH-HIVE.
    when I executed tpc-h-impala some error happens on
    q1_pricing_summary_report.
    error log is here:
    **********************************************************
    [fty@impala-server tpch_impala]$ impala-shell -i impala-server -f
    'q1_pricing_summary_report.impala'
    Connected to impala-server:21000
    Query: insert OVERWRITE TABLE q1_pricing_summary_report
    SELECT
    L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY),
    AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1)
    ^^^^^^^^^^
    CAST(COUNT(1) AS INT)
    FROM
    lineitem
    WHERE
    L_SHIPDATE<='1998-09-02'
    GROUP BY L_RETURNFLAG, L_LINESTATUS
    ORDER BY L_RETURNFLAG, L_LINESTATUS
    LIMIT 2147483647
    ERROR: com.cloudera.impala.common.AnalysisException: Analysis exception (in
    insert OVERWRITE TABLE q1_pricing_summary_report
    SELECT
    L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY),
    AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1)
    FROM
    lineitem
    WHERE
    L_SHIPDATE<='1998-09-02'
    GROUP BY L_RETURNFLAG, L_LINESTATUS
    ORDER BY L_RETURNFLAG, L_LINESTATUS
    LIMIT 2147483647)
    at
    com.cloudera.impala.analysis.AnalysisContext.analyze(AnalysisContext.java:133)
    at com.cloudera.impala.service.Frontend.createExecRequest(Frontend.java:221)
    at
    com.cloudera.impala.service.JniFrontend.createExecRequest(JniFrontend.java:91)
    Caused by: com.cloudera.impala.common.AnalysisException: Inserting into
    target table 'q1_pricing_summary_report' may result in loss of precision.
    Would need to cast '<slot 17>' to 'INT'.
    at
    com.cloudera.impala.analysis.InsertStmt.checkTypeCompatibility(InsertStmt.java:314)
    at
    com.cloudera.impala.analysis.InsertStmt.checkUnionCompatibility(InsertStmt.java:273)
    at com.cloudera.impala.analysis.InsertStmt.analyze(InsertStmt.java:124)
    at
    com.cloudera.impala.analysis.AnalysisContext.analyze(AnalysisContext.java:130)
    ... 2 more

    Could not execute command: insert OVERWRITE TABLE q1_pricing_summary_report
    SELECT
    L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY),
    AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1)
    FROM
    lineitem
    WHERE
    L_SHIPDATE<='1998-09-02'
    GROUP BY L_RETURNFLAG, L_LINESTATUS
    ORDER BY L_RETURNFLAG, L_LINESTATUS
    LIMIT 2147483647
    **********************************************************

    q1_pricing_summary_report file is here:
    **************************************************************
    INSERT OVERWRITE TABLE q1_pricing_summary_report
    SELECT
    L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY),
    AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1)
    FROM
    lineitem
    WHERE
    L_SHIPDATE<='1998-09-02'
    GROUP BY L_RETURNFLAG, L_LINESTATUS
    ORDER BY L_RETURNFLAG, L_LINESTATUS
    LIMIT 2147483647;
    **************************************************************

    TABLE q1_pricing_summary_report is here
    *******************************************************************
    -- create the target table
    CREATE TABLE q1_pricing_summary_report ( L_RETURNFLAG STRING, L_LINESTATUS
    STRING, SUM_QTY DOUBLE, SUM_BASE_PRICE DOUBLE, SUM_DISC_PRICE DOUBLE,
    SUM_CHARGE DOUBLE, AVE_QTY DOUBLE, AVE_PRICE DOUBLE, AVE_DISC DOUBLE,
    COUNT_ORDER INT);
    *******************************************************************

    I know there is something wrong with the type when INSERT, but from these
    files about the table and query I cannot find why this error happens.
    who can help me for this problem! Thanks a lot !!!

    Tianyuan
  • FU Tianyuan at Mar 14, 2013 at 3:10 pm
    Thank you Marcel!I think this may solve my problem. I will give you the
    result after I try again.
    在 2013-3-14 下午10:16,"Marcel Kornacker" <marcel@cloudera.com>写道:
    The problem is that in your Insert statement you're doing an implicit
    conversion from BIGINT to INT, which is illegal because it loses
    precision. You can get around that by doing an explicit CAST, as in
    On Thu, Mar 14, 2013 at 2:23 AM, FU Tianyuan wrote:
    Hi all,

    I have deployed a pseudo cluster on my CentOS 6.3 64bit, then I want to
    launch a tpc-h benchmark on it. After successfully load data, create table
    and TPCH-HIVE.
    when I executed tpc-h-impala some error happens on
    q1_pricing_summary_report.
    error log is here:
    **********************************************************
    [fty@impala-server tpch_impala]$ impala-shell -i impala-server -f
    'q1_pricing_summary_report.impala'
    Connected to impala-server:21000
    Query: insert OVERWRITE TABLE q1_pricing_summary_report
    SELECT
    L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY),
    AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1)
    ^^^^^^^^^^
    CAST(COUNT(1) AS INT)
    FROM
    lineitem
    WHERE
    L_SHIPDATE<='1998-09-02'
    GROUP BY L_RETURNFLAG, L_LINESTATUS
    ORDER BY L_RETURNFLAG, L_LINESTATUS
    LIMIT 2147483647
    ERROR: com.cloudera.impala.common.AnalysisException: Analysis exception (in
    insert OVERWRITE TABLE q1_pricing_summary_report
    SELECT
    L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY),
    AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1)
    FROM
    lineitem
    WHERE
    L_SHIPDATE<='1998-09-02'
    GROUP BY L_RETURNFLAG, L_LINESTATUS
    ORDER BY L_RETURNFLAG, L_LINESTATUS
    LIMIT 2147483647)
    at
    com.cloudera.impala.analysis.AnalysisContext.analyze(AnalysisContext.java:133)
    at
    com.cloudera.impala.service.Frontend.createExecRequest(Frontend.java:221)
    at
    com.cloudera.impala.service.JniFrontend.createExecRequest(JniFrontend.java:91)
    Caused by: com.cloudera.impala.common.AnalysisException: Inserting into
    target table 'q1_pricing_summary_report' may result in loss of precision.
    Would need to cast '<slot 17>' to 'INT'.
    at
    com.cloudera.impala.analysis.InsertStmt.checkTypeCompatibility(InsertStmt.java:314)
    at
    com.cloudera.impala.analysis.InsertStmt.checkUnionCompatibility(InsertStmt.java:273)
    at com.cloudera.impala.analysis.InsertStmt.analyze(InsertStmt.java:124)
    at
    com.cloudera.impala.analysis.AnalysisContext.analyze(AnalysisContext.java:130)
    ... 2 more

    Could not execute command: insert OVERWRITE TABLE
    q1_pricing_summary_report
    SELECT
    L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY),
    AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1)
    FROM
    lineitem
    WHERE
    L_SHIPDATE<='1998-09-02'
    GROUP BY L_RETURNFLAG, L_LINESTATUS
    ORDER BY L_RETURNFLAG, L_LINESTATUS
    LIMIT 2147483647
    **********************************************************

    q1_pricing_summary_report file is here:
    **************************************************************
    INSERT OVERWRITE TABLE q1_pricing_summary_report
    SELECT
    L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY), SUM(L_EXTENDEDPRICE),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)),
    SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)), AVG(L_QUANTITY),
    AVG(L_EXTENDEDPRICE), AVG(L_DISCOUNT), COUNT(1)
    FROM
    lineitem
    WHERE
    L_SHIPDATE<='1998-09-02'
    GROUP BY L_RETURNFLAG, L_LINESTATUS
    ORDER BY L_RETURNFLAG, L_LINESTATUS
    LIMIT 2147483647;
    **************************************************************

    TABLE q1_pricing_summary_report is here
    *******************************************************************
    -- create the target table
    CREATE TABLE q1_pricing_summary_report ( L_RETURNFLAG STRING,
    L_LINESTATUS
    STRING, SUM_QTY DOUBLE, SUM_BASE_PRICE DOUBLE, SUM_DISC_PRICE DOUBLE,
    SUM_CHARGE DOUBLE, AVE_QTY DOUBLE, AVE_PRICE DOUBLE, AVE_DISC DOUBLE,
    COUNT_ORDER INT);
    *******************************************************************

    I know there is something wrong with the type when INSERT, but from these
    files about the table and query I cannot find why this error happens.
    who can help me for this problem! Thanks a lot !!!

    Tianyuan

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupimpala-user @
categorieshadoop
postedMar 14, '13 at 9:23a
activeMar 14, '13 at 3:10p
posts3
users2
websitecloudera.com
irc#hadoop

2 users in discussion

FU Tianyuan: 2 posts Marcel Kornacker: 1 post

People

Translate

site design / logo © 2022 Grokbase