title: Fast time series bulk insert with cassandra and python
body: <p>I am trying to find the fastest way to insert time series data in Cassandra, using the cassandra python client. I am running on localhost.</p>
  
  <p>The preparatory code looks like:</p>
  
  <pre><code>session.execute("CREATE KEYSPACE testt WITH replication = {'class' : 'SimpleStrategy', 'replication_factor': '1'}")
  session.set_keyspace('testt')
  session.execute('CREATE TABLE t (id text, time timestamp, t float, PRIMARY KEY (id, time))')
  
  prepared = session.prepare("""
      INSERT INTO t (id, time, t)
      VALUES (?, ?, ?)
      """)
  
  index = pd.date_range("2003-01-01", "2004-01-01", freq="1min")
  t = np.random.rand(len(index))*2-1
  df = pd.DataFrame({'time': index, 't': t}).set_index('time')
  
  print(len(df))
  # 525601
  </code></pre>
  
  <p>And then I have tried two strategies. The first is using BatchStatements:</p>
  
  <pre><code>before = datetime.datetime.now()
  x = 0
  delta = 65535
  while x &lt; len(df):
      batch = BatchStatement(cassandra.query.BatchType.UNLOGGED)
      for i, r in df.iloc[x : x + delta].iterrows():
          batch.add(prepared, ("ID1", i, r['t']))
      session.execute(batch)    
      x += delta  
  print("Elapsed: {}".format(datetime.datetime.now() - before)) 
  # Elapsed: 0:01:01.341848
  </code></pre>
  
  <p>Note that the 65535 jumps are due to an upper limit on the size of a BatchStatement. </p>
  
  <p>.. and the second one is using execute_async:</p>
  
  <pre><code>before = datetime.datetime.now()
  for i, r in df.iterrows():
      session.execute_async(prepared.bind(("ID1", i, r['t'])))
  print("Elapsed: {}".format(datetime.datetime.now() - before)) 
  # Elapsed: 0:03:51.169409
  </code></pre>
  
  <p>I have also tried the same scenario with InfluxDB (using its DataFrame client), where the same insertion takes about 5 seconds. So my question is if fell victim to some kind of anti-pattern (I read a few of the blog posts, but seem to be doing things  OK), or if this type of insertion is simply x12 slower using Cassandra.  </p>
  
  <p>There's room for scaling up to more server nodes - or python multiprocessing etc on  the client side - but I wanted to understand the single-node performance first.</p>
  
  <p>Any pointers would be much appreciated!</p>