Couldn't exec impala function at INSERT Statement

[zbyjm6969]

Just like title, When I exec insert statement like insert into table TABLE_A (id,user_name) select id,user_name from TABLE_B

It good to work, But if I give a function like insert into table TABLE_A (id,user_name,age) select id,user_name,cast(age as string) from TABLE_B No fail message and no data insert

[shaloi]

Hi,

I tried to reproduce your issue as follows:

TABLE A:
+-----------------------+----------+---------+
| name                  | type     | comment |
+-----------------------+----------+---------+
| id             | int      |         |
| name           | string   |         |
| age            | smallint |         |
| sex            | string   |         |
+-----------------------+----------+---------+

TABLE B:
+-----------------------+----------+---------+
| name                  | type     | comment |
+-----------------------+----------+---------+
| id             | int      |         |
| name           | string   |         |
| age            | string |         |
| sex            | string   |         |
+-----------------------+----------+---------+

I tried to insert data from table_a to table_b using the following code:

package main

import (
    "context"
    "database/sql"
    "log"

    impala "github.com/bippio/go-impala"
)

func main() {

    opts := impala.DefaultOptions

    opts.Host = "Impala Host"
    opts.Port = "21050"

    // enable LDAP authentication:
    opts.UseLDAP = true
    opts.Username = "user"
    opts.Password = "pass"

    // enable TLS
    opts.UseTLS = true
    opts.CACertPath = "/path/to.cert.pem"

    connector := impala.NewConnector(&opts)
    db := sql.OpenDB(connector)
    defer db.Close()

    ctx := context.Background()

    _, err := db.QueryContext(ctx, "insert into default.table_b(id, name, age, sex) select id, name, cast(age as string), sex from default.table_a")
    if err != nil {
        log.Fatal(err)
    }
}

and it worked as expected. The data were inserted to table_b from table_a.

If I try use an incompatible column with cast, the program throws error like below:

2020/11/06 19:15:29 AnalysisException: Target table 'default.table_b' is incompatible with source expressions.
Expression 'CAST(id AS STRING)' (type: STRING) is not compatible with column 'id' (type: INT)
exit status 1

Have you tried running your query through Hue or Impala Shell? What error does that throw?

[zbyjm6969]

Hi,

I tried to reproduce your issue as follows:

TABLE A:
+-----------------------+----------+---------+
| name                  | type     | comment |
+-----------------------+----------+---------+
| id             | int      |         |
| name           | string   |         |
| age            | smallint |         |
| sex            | string   |         |
+-----------------------+----------+---------+

TABLE B:
+-----------------------+----------+---------+
| name                  | type     | comment |
+-----------------------+----------+---------+
| id             | int      |         |
| name           | string   |         |
| age            | string |         |
| sex            | string   |         |
+-----------------------+----------+---------+

I tried to insert data from table_a to table_b using the following code:

package main

import (
    "context"
    "database/sql"
    "log"

    impala "github.com/bippio/go-impala"
)

func main() {

    opts := impala.DefaultOptions

    opts.Host = "Impala Host"
    opts.Port = "21050"

    // enable LDAP authentication:
    opts.UseLDAP = true
    opts.Username = "user"
    opts.Password = "pass"

    // enable TLS
    opts.UseTLS = true
    opts.CACertPath = "/path/to.cert.pem"

    connector := impala.NewConnector(&opts)
    db := sql.OpenDB(connector)
    defer db.Close()

    ctx := context.Background()

    _, err := db.QueryContext(ctx, "insert into default.table_b(id, name, age, sex) select id, name, cast(age as string), sex from default.table_a")
    if err != nil {
        log.Fatal(err)
    }
}

and it worked as expected. The data were inserted to table_b from table_a.

If I try use an incompatible column with cast, the program throws error like below:

2020/11/06 19:15:29 AnalysisException: Target table 'default.table_b' is incompatible with source expressions.
Expression 'CAST(id AS STRING)' (type: STRING) is not compatible with column 'id' (type: INT)
exit status 1

Have you tried running your query through Hue or Impala Shell? What error does that throw?

After my test, It not work again. I running my query via impala-shell, And no error throw.

I use go mod in my project, Do you think that is problem?

Thanks.