CODEWITHSUNDEEP
odbcdriverimpala
marley1990
marley1990

Reputation: 25

Impala ODBC Driver for Multiple Insert

I have a problem with multiple inserts via odbc Impala (last version).

I am trying to insert two thousand rows in a Impala table (on Cloudera) with 4 columns but I receive two errors :

[Cloudera][SQLEngine] (31580) The length of the statement exceeds the maximum: 16384. [Cloudera][ImpalaODBC] (110) Error while executing a query in Impala: [HY000] : AnalysisException: Syntax error in line 1: ...

I'm using query parameterized query :

INSERT INTO name_table (a,b,c,d) VALUES (?,?,?,?) , (?,?,?,?) , ....

Now my question is :

Is there a way to insert millions of rows using the odbc driver without using single inserts?

Thank you.

Upvotes: 0

Views: 1522

Answers (2)

marley1990
marley1990

Reputation: 25

I had already tried to execute the following code:

        OdbcCommand oComm = oConn.CreateCommand();

        StringBuilder sb = new StringBuilder();


        sb.AppendFormat("INSERT INTO {0}.{1} (a,b,c,d) VALUES (?,?,?,?)", dbName, tableName);



        List<OdbcParameter> psl = new List<OdbcParameter>(1000 * 4);

        for (int i = 0; i < 1000; i++)
        {

            var odbcParameter = new OdbcParameter();
            odbcParameter.ParameterName = "@a";
            odbcParameter.OdbcType = OdbcType.Text;
            odbcParameter.Value ="a"+i;
            psl.Add(odbcParameter);

            odbcParameter = new OdbcParameter();
            odbcParameter.ParameterName = "@b";
            odbcParameter.OdbcType = OdbcType.Text;
            odbcParameter.Value = "b" + i;
            psl.Add(odbcParameter);

            odbcParameter = new OdbcParameter();
            odbcParameter.ParameterName = "@c";
            odbcParameter.OdbcType = OdbcType.Text;
            odbcParameter.Value = "c" + i;
            psl.Add(odbcParameter);

            odbcParameter = new OdbcParameter();
            odbcParameter.ParameterName = "@d";
            odbcParameter.Value = "d" + i;
            odbcParameter.OdbcType = OdbcType.Text;
            psl.Add(odbcParameter);


        }


        oComm.Parameters.AddRange(psl.ToArray());

        oComm.CommandText = sb.ToString();

        oComm.executeNonQUery();

At the end, my table is composed from only one record with the first four parameter's values.

There is something wrong in my code?

Thanks.

Upvotes: 0

KylePorter
KylePorter

Reputation: 499

At the moment, there's a maximum of 16384 characters for a query, which should be what looks like a much higher limit. We're currently investigating to see if there's a reason for this low limit.

That said, a more efficient way of inserting multiple values via ODBC would be to use the query:

INSERT INTO name_table (a,b,c,d) VALUES (?,?,?,?)

and then use array binding to bind multiple parameter sets to your parameters. In this way, the driver can optimize the execution internally and you don't run into a query length limitation. Have you tried this already?

Upvotes: 0

Related Questions