Execute SQL script - bulk operations

[cyrixsimon]

Hi,

there should be a function that enables the execution of an entire SQL script. This would make it possible to perform mass operations. At the moment, each statement has to be executed individually, which results in very slow processing for a large SQL file. No bulk insertion or other bulk operations can be performed with acceptable performance. The exasol websocket interface is too slow in the moment to handle a large amount of SQL statements.

[Nicoretti]

Hi @cyrixsimon,

Thanks for the suggestion, you raise a good point. We will discuss it and see how it fits into our planing and outline for the future.

Best,
Nico

[kaklakariada]

Hi @cyrixsimon , I am not sure what your use case is. But If you are inserting many rows, a solution similar like this could help you: https://stackoverflow.com/a/25192138

func BulkInsert(unsavedRows []*ExampleRowStruct) error {
    valueStrings := make([]string, 0, len(unsavedRows))
    valueArgs := make([]interface{}, 0, len(unsavedRows) * 3)
    for _, post := range unsavedRows {
        valueStrings = append(valueStrings, "(?, ?, ?)")
        valueArgs = append(valueArgs, post.Column1)
        valueArgs = append(valueArgs, post.Column2)
        valueArgs = append(valueArgs, post.Column3)
    }
    stmt := fmt.Sprintf("INSERT INTO my_sample_table (column1, column2, column3) VALUES %s", 
                        strings.Join(valueStrings, ","))
    _, err := db.Exec(stmt, valueArgs...)
    return err
}

[cyrixsimon]

Hi,

that's right, that would be a solution if I only have inserts. But there are also use cases where you want to perform arbitrary operations. So I have a SQL script with massive inserts (>100000), deletes (>10000) and updates (>50000). I want to execute this in a perfomant way. At the moment I can't use the Go driver to handle such a use case as the processing is very slow and poor.

I have looked at the WebSocket specification and there is exactly such functionality - see https://github.com/exasol/websocket-api/blob/master/docs/commands/executeBatchV1.md. The executeBatch command should handle massive bulk operations in a performant way.

[kaklakariada]

Thank you for the explanation. This is a valid use case, I will talk about this with product management.

[peterkioko]

Hi @cyrixsimon

I don't know if you have the ability to create local files but if so you may want to consider writing the data to a file and then using the IMPORT statement for bulk DML e.g.

-- Inserts
IMPORT INTO t FROM LOCAL CSV FILE 'data.csv'
    COLUMN SEPARATOR = ','
    ENCODING = 'UTF-8'
    ROW SEPARATOR = 'LF'

-- Updates
MERGE INTO t USING (
    IMPORT INTO (id int, val int) FROM LOCAL CSV FILE 'data.csv'
        COLUMN SEPARATOR = ','
        ENCODING = 'UTF-8'
        ROW SEPARATOR = 'LF'
) AS u ON u.id = t.id
WHEN MATCHED THEN UPDATE SET t.val = u.val

-- Deletes
DELETE FROM t WHERE (id, val) IN (
    IMPORT INTO (id int,val int) FROM LOCAL CSV FILE 'data.csv'
        COLUMN SEPARATOR = ','
        ENCODING = 'UTF-8'
        ROW SEPARATOR = 'LF'
)

It is also possible to combine all three operations into a single MERGE statement. This scales fairly well.

@kaklakariada - It would be great if you could add support for IMPORT/EXPORT from/to a Go channel containing the data (instead of a local file). Perhaps something similar to what the old legacy Go driver did at https://github.com/GrantStreetGroup/go-exasol-client/blob/master/README.md?plain=1#L69 (though I don't recommend that particular implementation)

Regards,
Peter

[kaklakariada]

@peterkioko thanks for your proposal! I created #122 to keep it separated from this. Feel free to update the issue with details you would like to see. I will talk with product management if/when we can implement this.