ColumnStoreBulkInsert Class¶
-
class
ColumnStoreBulkInsert
¶ The bulk insert class is designed to rapidly insert data into a ColumnStore installation.
Note
An instance of this class should only be created from
ColumnStoreDriver
Note
If an explicit commit is not given before the class is destroyed then an implicit rollback will be executed
Note
This class should be viewed as a single transaction. Once committed or rolled back the class cannot be used for any more operations beyond getting the summary. Further usage attempts will result in an exception being thrown.
getColumnCount()¶
-
ColumnStoreBulkInsert.
getColumnCount
()¶ Gets the number of columns in the table to be inserted into.
Returns: A count of the number of columns
Example¶
This example can be used inside the try…except blocks in the ColumnStoreDriver
examples.
1 2 3 4 5 6 | ...
driver = pymcsapi.ColumnStoreDriver()
bulkInsert = driver.createBulkInsert(db, table, 0, 0)
# columnCount will now contain the number of columns in the table
columnCount = bulkInsert.getColumnCount()
...
|
setColumn()¶
-
ColumnStoreBulkInsert.
setColumn
(columnNumber, value)¶ Sets a value for a given column.
Parameters: - columnNumber – The column number to set (starting from
0
) - value – The value to set this column
Returns: A List of a pointer to the
ColumnStoreBulkInsert
class so that calls can be chained, and the return status.Raises: - RuntimeError – If there is an error setting the column, such as truncation error when
ColumnStoreBulkInsert.setTruncateIsError()
is used or an invalid column number is supplied - RuntimeError – If the transaction has already been closed
- columnNumber – The column number to set (starting from
Example¶
This example can be used inside the try…except blocks in the ColumnStoreDriver
examples.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | ...
driver = pymcsapi.ColumnStoreDriver()
bulkInsert = driver.createBulkInsert(db, table, 0, 0)
# Create a decimal value
decimalVal = pymcsapi.ColumnStoreDecimal("3.14159")
# And an int value
intVal = 123456
# And a string value
strVal = "Hello World"
# Finally a date/time values
dateTime = pymcsapi.ColumnStoreDateTime("1999-01-01 23:23:23", "%Y-%m-%d %H:%M:%S")
nxt, status = bulkInsert.setColumn(0, intVal)
# Check conversion status
if status != pymcsapi.CONVERT_STATUS_NONE:
exit(1)
nxt, status = bulkInsert.setColumn(1, decimalVal)
# Check conversion status
if status != pymcsapi.CONVERT_STATUS_NONE:
exit(1)
nxt, status = bulkInsert.setColumn(2, strVal)
# Check conversion status
if status != pymcsapi.CONVERT_STATUS_NONE:
exit(1)
nxt, status = bulkInsert.setColumn(3, dateTime)
# Check conversion status
if status != pymcsapi.CONVERT_STATUS_NONE:
exit(1)
# Write this row ready to start another
bulkInsert.writeRow()
decimalVal.set("1.41421")
intVal = 654321
strVal = "dlroW olleH"
dateTime.set("2017-07-05 22:00:43", "%Y-%m-%d %H:%M:%S")
# A chained example
bulkInsert.setColumn(0, intVal)[0].setColumn(1, decimalVal)[0].setColumn(2, strVal)[0].setColumn(3, dateTime)[0].writeRow()
...
|
setNull()¶
-
ColumnStoreBulkInsert.
setNull
(columnNumber)¶ Sets a
NULL
for a given column.Parameters: columnNumber – The column number to set (starting from
0
)Returns: A List of a pointer to the
ColumnStoreBulkInsert
class so that calls can be chained, and the return statusRaises: - RuntimeError – If there is an error setting the column, such as an invalid column number is supplied
- RuntimeError – If the transaction has already been closed
Example¶
This example can be used inside the try…except blocks in the ColumnStoreDriver
examples.
1 2 3 4 5 6 7 | ...
driver = pymcsapi.ColumnStoreDriver()
bulkInsert = driver.createBulkInsert(db, table, 0, 0)
# Set an whole row of NULLs
bulkInsert.setNull(0)[0].setNull(1)[0].setNull(2)[0].setNull(3)[0].writeRow()
...
|
resetRow()¶
-
ColumnStoreBulkInsert.
resetRow
()¶ Resets everything that has been set for the current row. This method should be used to clear the row memory without using
ColumnStoreBulkInsert.writeRow()
.Returns: A pointer to the ColumnStoreBulkInsert
class so that calls can be chainedRaises: RuntimeError – If the transaction has already been closed
writeRow()¶
-
ColumnStoreBulkInsert.
writeRow
()¶ States that a row is ready to be written.
Note
The row may not be written at this stage. The library will batch an amount of rows together before sending them, by default data is only sent to the server every 100,000 rows or
ColumnStoreBulkInsert.commit()
is called. Data is not committed withwriteRow()
, it has to be explicitly committed at the end of the transaction.Returns: A pointer to the
ColumnStoreBulkInsert
class so that calls can be chainedRaises: - RuntimeError – If there has been an error during the write at the network level
- RuntimeError – If there has been an error during the write at the remote server level
- RuntimeError – If the transaction has already been closed
Example¶
This example can be used inside the try…except blocks in the ColumnStoreDriver
examples.
1 2 3 4 5 6 7 8 9 10 11 | ...
driver = pymcsapi.ColumnStoreDriver()
bulkInsert = driver.createBulkInsert(db, table, 0, 0)
# Set values for a 2 int column table
bulkInsert.setColumn(0, 123456)
bulkInsert.setColumn(1, 654321)
# Write the row
bulkInsert.writeRow()
...
|
commit()¶
-
ColumnStoreBulkInsert.
commit
()¶ Commits the data to the table.
Note
After making this call the transaction is completed and the class should not be used for anything but
ColumnStoreBulkInsert.getSummary()
orColumnStoreBulkInsert.isActive()
. Attempts to use it again will trigger an exception.Note
If the commit fails a rollback will be executed automatically upon deletion of the
ColumnStoreBulkInsert
object.Raises: - RuntimeError – If there has been an error during the write at the network level
- RuntimeError – If there has been an error during the write at the remote server level
- RuntimeError – If the transaction has already been closed
Example¶
This example can be used inside the try…except blocks in the ColumnStoreDriver
examples.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | ...
driver = pymcsapi.ColumnStoreDriver()
bulkInsert = driver.createBulkInsert(db, table, 0, 0)
# Set values for a 2 int column table
bulkInsert.setColumn(0, 123456)
bulkInsert.setColumn(1, 654321)
# Write the row
bulkInsert.writeRow()
# Commit the transaction
bulkInsert.commit()
# This WILL throw an exception if uncommented
# bulkInsert.setColumn(0, 99999)
...
|
rollback()¶
-
ColumnStoreBulkInsert.
rollback
()¶ Rolls back the data written to the table. If the transaction has already been committed or rolled back this will just return without error.
Note
After making this call the transaction is completed and the class should not be used for anything but
ColumnStoreBulkInsert.getSummary()
orColumnStoreBulkInsert.isActive()
. Attempts to use it again will trigger an exception.Raises: - RuntimeError – If there has been an error during the write at the network level
- RuntimeError – If there has been an error during the write at the remote server level
Example¶
This example can be used inside the try…except blocks in the ColumnStoreDriver
examples.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | ...
driver = pymcsapi.ColumnStoreDriver()
bulkInsert = driver.createBulkInsert(db, table, 0, 0)
# Set values for a 2 int column table
bulkInsert.setColumn(0, 123456)
bulkInsert.setColumn(1, 654321)
# Write the row
bulkInsert.writeRow()
# Rollback the transaction
bulkInsert.rollback()
# This WILL throw an exception if uncommented
# bulkInsert.setColumn(0, 99999)
...
|
isActive()¶
-
ColumnStoreBulkInsert.
isActive
()¶ Returns whether or not the bulk insert transaction is still active.
Returns: True if the transaction is still active, False if it has been committed or rolled back
getSummary()¶
-
ColumnStoreBulkInsert.
getSummary
()¶ Gets the summary information for this bulk write transaction.
Returns: The summary object
Example¶
This example can be used inside the try…except blocks in the ColumnStoreDriver
examples.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | ...
driver = pymcsapi.ColumnStoreDriver()
bulkInsert = driver.createBulkInsert(db, table, 0, 0)
# Set values for a 2 int column table
bulkInsert.setColumn(0, 123456)
bulkInsert.setColumn(1, 654321)
# Write the row
bulkInsert.writeRow()
# Rollback the transaction
bulkInsert.rollback()
# Get the summary
summary = bulkInsert.getSummary()
# Get the number of inserted rows before they were rolled back
rows = summary.getRowsInsertedCount()
...
|
setTruncateIsError()¶
-
ColumnStoreBulkInsert::setTruncateIsError(set)
Sets whether or not a truncation of CHAR/VARCHAR data is an error. It is disabled by default.
Parameters: set – True to enable, False to disable
Example¶
This example can be used inside the try…except blocks in the ColumnStoreDriver
examples.
1 2 3 4 5 6 7 8 9 10 11 12 | ...
driver = pymcsapi.ColumnStoreDriver()
bulkInsert = driver.createBulkInsert(db, table, 0, 0)
bulkInsert.setTruncateIsError(True)
# A short string that will insert fine
bulkInsert.setColumn(0, "Short string")
# This long string will truncate on my VARCHAR(20) and throw an exception
bulkInsert.setColumn(1, "This is a long string test to demonstrate setTruncateIsError()")
...
|