Vertica ODBC bulk load from memory?

Comments

• Options

  Daniel_Leybovic

  June 2014

  Hi!
  
  1. Yes, for example data on ram disk has a valid file descriptor (/dev/shm) or create memory mapped file or named pipe and stream data to pipe while copy from pipe (from file descriptor). You can do it with sockets (sockets are valid file descriptors too), stream to socket -> COPY reads from socket. Data source can be anything(while streaming) - buffer, mmap, file, whatever
  
  2. prepared statements automatically converted to COPY (with AUTOCOMMIT off)
  
  For example (vertica odbc log, level - trace):

  Apr 30 19:20:38 DEBUG 743249728 VQueryExecutor::ProcessParams: sqltype=-5, Oidtype=6, Len=1
  Apr 30 19:20:38 TRACE 743249728 VQueryExecutor::Execute: AUTOCOMMIT property is off, leaving COPY running
  Apr 30 19:20:38 TRACE 743249728 VQueryExecutor::GetResults: +++++ enter +++++
  
  

  Batch Insert Steps

  The steps your application needs to take in order to perform an ODBC Batch Insert are:

  1. Connect to the database.
  2. Disable autocommit for the connection.
  3. Create a prepared statement that inserts the data you want to load.
  4. Bind the parameters of the prepared statement to arrays that will contain the data you want to load.
  5. Populate the arrays with the data for your batches.
  6. Execute the prepared statement.
  7. Optionally, check the results of the batch load to find rejected rows.
  8. Repeat the previous three steps until all of the data you want to load is loaded.
  9. Commit the transaction.
  10. Optionally, check the results of the entire batch transaction.

  The following example code demonstrates a simplified version of the above steps.
  ...
  https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/NewFeatures/4.0/UsingBatchInserts.htm%3FT...
  
  
  Using Prepared Statements

  Server-side prepared statements are useful for:

  • Optimizing queries. HP Vertica only needs to parse the statement once.
  • Preventing SQL injection attacks. A SQL injection attack occurs when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly run. Since a prepared statement is parsed separately from the input data, there is no chance the data can be accidentally executed by the database.
  • Binding direct variables to return columns. By pointing to data structures, the code doesn't have to perform extra transformations.

  https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/ProgrammersGuide/ClientDriverMisc/UsingPr...
  

  0
• Options

  Mark_Andrews

  June 2014

  thanks for the reply, I'll pay attention to that odbc log with -trace
  

  0
• Options

  Daniel_Leybovic

  June 2014

  Hi!
  
  Yes, with trace you can see all your actions and how them interpreted by driver. Sometimes very useful.
   
  An odbc session:

  In [13]: conn = pyodbc.connect(dsn='v7', user='daniel', autocommit=False)
  
  In [14]: cursor = conn.cursor()
  
  In [15]: cursor.executemany('insert into test values(?)', [(1,), (2,), (3,)])
  
  In [16]: cursor.execute('select get_num_accepted_rows()')
  Out[16]: <pyodbc.Cursor at 0x16b2cf0>
  
  In [17]: cursor.fetchone()
  Out[17]: (3L, )
  
  In [18]: cursor.commit()
  
  In [20]: conn.close()
  
  In [21]:
  

  Trace (a lot of lines omitted):

  ... VConnection::Connect: +++++ enter +++++
  ... VConnection::Connect: Connect to host :5433, db dev
  ...
  ... VConnection::BeginTransaction: +++++ enter +++++
  ...
  ... VDataEngine::Prepare: +++++ enter +++++
  ... VDataEngine::Prepare: Original Query: insert into test values(?)
  ... VDataEngine::Prepare: Query: insert into test values(?)
  ... VQueryExecutor::VQueryExecutor: +++++ enter +++++
  ... VDataEngine::Prepare: Initializing prepared statement: _PLAN0x19857e0_0
  ... VQueryExecutor::InitializePreparedExecutor: +++++ enter +++++
  ... VQueryExecutor::ClearCancel: +++++ enter +++++
  ... VQueryExecutor::PopulateParameters: +++++ enter +++++
  ...
  ... VQueryExecutor::ProcessParams: sqltype=-5, Oidtype=6, Len=1
  ... VQueryExecutor::Execute: AUTOCOMMIT property is off, leaving COPY running
  ... VQueryExecutor::GetResults: +++++ enter +++++
  ...
  ... VQueryExecutor::FinishStreamingExecution: +++++ enter +++++
  ...
  ... VDataEngine::Prepare: +++++ enter +++++
  ... VDataEngine::Prepare: Original Query: select get_num_accepted_rows()
  ... VDataEngine::Prepare: Query is issued as ExecDirect
  ... VDataEngine::Prepare: Query: select get_num_accepted_rows()
  ... VQueryExecutor::VQueryExecutor: +++++ enter +++++
  ... VDataEngine::Prepare: Initializing prepared statement: _PLAN0x1986700_1
  ... VQueryExecutor::InitializePreparedExecutor: +++++ enter +++++
  ...
  ... VBaseResultSet::DoMoveToNext: +++++ enter +++++
  ... VBaseResultSet::DoMoveToNext: m_rowItr: -1, m_currentResultSetSize: 1, resultStatus: 2
  ... VPQResultSet::GetSelectColumns: +++++ enter +++++
  ... VPQResultSet::GetData: +++++ enter +++++
  ... Connection::SQLEndTran: +++++ enter +++++
  ...
  ... Connection::SQLDisconnect: +++++ enter +++++
  ... ConnectionState4::SQLDisconnect: +++++ enter +++++
  ... VConnection::Disconnect: +++++ enter +++++

  0