def store_qc_results(qc_results_values, pk, particle_ids, particle_bins, particle_deploys,
param_name, strict_range=False):
start_time = time.clock()
if engine.app.config['QC_RESULTS_STORAGE_SYSTEM'] == 'cass':
log.info('Storing QC results in Cassandra.')
insert_results = SessionManager.prepare(
"insert into ooi.qc_results "
"(subsite, node, sensor, bin, deployment, stream, id, parameter, results) "
"values (?, ?, ?, ?, ?, ?, ?, ?, ?)")
batch = BatchStatement()
for (qc_results, particle_id, particle_bin, particle_deploy) in izip(qc_results_values, particle_ids,
particle_bins, particle_deploys):
batch.add(insert_results, (pk.get('subsite'), pk.get('node'), pk.get('sensor'),
particle_bin, particle_deploy, pk.get('stream'),
uuid.UUID(particle_id), param_name, str(qc_results)))
SessionManager.session().execute_async(batch)
log.info("QC results stored in {} seconds.".format(time.clock() - start_time))
elif engine.app.config['QC_RESULTS_STORAGE_SYSTEM'] == 'log':
log.info('Writing QC results to log file.')
qc_log = logging.getLogger('qc.results')
qc_log_string = ""
for (qc_results, particle_id, particle_bin, particle_deploy) in izip(qc_results_values, particle_ids,
particle_bins, particle_deploys):
qc_log_string += "refdes:{0}-{1}-{2}, bin:{3}, stream:{4}, deployment:{5}, id:{6}, parameter:{7}, qc results:{8}\n" \
.format(pk.get('subsite'), pk.get('node'), pk.get('sensor'), particle_bin,
pk.get('stream'), particle_deploy, particle_id, param_name, qc_results)
qc_log.info(qc_log_string[:-1])
log.info("QC results stored in {} seconds.".format(time.clock() - start_time))
else:
log.info("Configured storage system '{}' not recognized, qc results not stored.".format(
engine.app.config['QC_RESULTS_STORAGE_SYSTEM']))
def _delete_task_and_index(self, task):
""" Deletes a task and its index atomically.
Args:
task: A Task object.
"""
batch_delete = BatchStatement(retry_policy=self.db_access.retry_policy)
delete_task = SimpleStatement(
"""
DELETE FROM pull_queue_tasks
WHERE app = %(app)s AND queue = %(queue)s AND id = %(id)s
"""
)
parameters = {"app": self.app, "queue": self.name, "id": task.id}
batch_delete.add(delete_task, parameters=parameters)
delete_task_index = SimpleStatement(
"""
DELETE FROM pull_queue_tasks_index
WHERE app = %(app)s
AND queue = %(queue)s
AND eta = %(eta)s
AND id = %(id)s
"""
)
parameters = {"app": self.app, "queue": self.name, "eta": task.get_eta(), "id": task.id}
batch_delete.add(delete_task_index, parameters=parameters)
self.db_access.session.execute(batch_delete)
def _normal_batch(self, mutations):
""" Use Cassandra's native batch statement to apply mutations atomically.
Args:
mutations: A list of dictionaries representing mutations.
"""
self.logger.debug("Normal batch: {} mutations".format(len(mutations)))
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM, retry_policy=self.retry_policy)
prepared_statements = {"insert": {}, "delete": {}}
for mutation in mutations:
table = mutation["table"]
if mutation["operation"] == TxnActions.PUT:
if table not in prepared_statements["insert"]:
prepared_statements["insert"][table] = self.prepare_insert(table)
values = mutation["values"]
for column in values:
batch.add(
prepared_statements["insert"][table],
(bytearray(mutation["key"]), column, bytearray(values[column])),
)
elif mutation["operation"] == TxnActions.DELETE:
if table not in prepared_statements["delete"]:
prepared_statements["delete"][table] = self.prepare_delete(table)
batch.add(prepared_statements["delete"][table], (bytearray(mutation["key"]),))
try:
self.session.execute(batch)
except (cassandra.Unavailable, cassandra.Timeout, cassandra.CoordinationFailure, cassandra.OperationTimedOut):
message = "Exception during batch_mutate"
logging.exception(message)
raise AppScaleDBConnectionError(message)
def insert_rows(starting_partition, ending_partition, rows_per_partition, counter, counter_lock):
cluster = Cluster(['127.0.0.1'], load_balancing_policy=TokenAwarePolicy(RoundRobinPolicy()))
try:
session = cluster.connect('ks')
try:
statement = session.prepare('INSERT INTO tbl (a, b, c, d) VALUES (?, ?, ?, ?)')
for partition_key in xrange(starting_partition, ending_partition):
batch = None
batch_size = 0
for cluster_column in xrange(rows_per_partition):
if batch is None:
batch = BatchStatement(batch_type=BatchType.UNLOGGED)
value1 = random.randint(1, 1000000)
value2 = random.randint(1, 1000000)
batch.add(statement, [partition_key, cluster_column, value1, value2])
batch_size += 1
if (batch_size == MAX_BATCH_SIZE) or (cluster_column + 1 == rows_per_partition):
with counter_lock:
counter.value += batch_size
session.execute(batch)
batch = None
batch_size = 0
finally:
session.shutdown()
finally:
cluster.shutdown()
def _normal_batch(self, mutations):
""" Use Cassandra's native batch statement to apply mutations atomically.
Args:
mutations: A list of dictionaries representing mutations.
"""
self.logger.debug('Normal batch: {} mutations'.format(len(mutations)))
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM,
retry_policy=self.retry_policy)
prepared_statements = {'insert': {}, 'delete': {}}
for mutation in mutations:
table = mutation['table']
if mutation['operation'] == TxnActions.PUT:
if table not in prepared_statements['insert']:
prepared_statements['insert'][table] = self.prepare_insert(table)
values = mutation['values']
for column in values:
batch.add(
prepared_statements['insert'][table],
(bytearray(mutation['key']), column, bytearray(values[column]))
)
elif mutation['operation'] == TxnActions.DELETE:
if table not in prepared_statements['delete']:
prepared_statements['delete'][table] = self.prepare_delete(table)
batch.add(
prepared_statements['delete'][table],
(bytearray(mutation['key']),)
)
try:
self.session.execute(batch)
except dbconstants.TRANSIENT_CASSANDRA_ERRORS:
message = 'Exception during batch_mutate'
logging.exception(message)
raise AppScaleDBConnectionError(message)
def set_latest_batch_time(vineyard_id, hub_id, batch_sent, hub_data):
"""
Inserts timestamp for latest data, received from a hub, into the database.
"""
table = os.environ.get('DB_HW_TABLE')
parameters = {
'vineid': vineyard_id,
'hubid': hub_id,
'lasthubbatchsent': batch_sent,
}
query = (
'UPDATE {} SET lasthubbatchsent=? '
'WHERE vineid=? AND hubid=? AND nodeid=?;'
)
prepared_statement = session.prepare(
query.format(table)
)
batch_statement = BatchStatement()
try:
for data_point in hub_data:
parameters['nodeid'] = int(data_point['node_id'])
batch_statement.add(
prepared_statement,
parameters
)
session.execute(batch_statement)
return True
# Unknown exception
except Exception as e:
raise Exception('Transaction Error Occurred: '.format(str(e)))
def put_entities_tx(self, app, txid, entities):
""" Update transaction metadata with new put operations.
Args:
app: A string containing an application ID.
txid: An integer specifying the transaction ID.
entities: A list of entities that will be put upon commit.
"""
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM,
retry_policy=BASIC_RETRIES)
insert = self.session.prepare("""
INSERT INTO transactions (txid_hash, operation, namespace, path, entity)
VALUES (?, ?, ?, ?, ?)
USING TTL {ttl}
""".format(ttl=dbconstants.MAX_TX_DURATION * 2))
for entity in entities:
args = (tx_partition(app, txid),
TxnActions.MUTATE,
entity.key().name_space(),
bytearray(entity.key().path().Encode()),
bytearray(entity.Encode()))
batch.add(insert, args)
try:
self.session.execute(batch)
except dbconstants.TRANSIENT_CASSANDRA_ERRORS:
message = 'Exception while putting entities in a transaction'
logging.exception(message)
raise AppScaleDBConnectionError(message)
def _upsert_users(keyspace):
try:
_cluster = Cluster(contact_points=CASSANDRA_CLUSTER, port=9042)
_session = _cluster.connect()
_session.set_keyspace(keyspace)
_session.row_factory = dict_factory
_add_users = [
{'userid': 'dummy1', 'first_name': '11', 'last_name': 'dummy', 'emails': set('a')},
{'userid': 'dummy2', 'first_name': '22', 'last_name': 'dummy', 'emails': set('b')}
]
_prepare_insert = _session.prepare(
"INSERT INTO users (userid, first_name, last_name, emails) VALUES (?, ?, ?, ?)")
# "INSERT INTO users (userid, first_name, emails) VALUES (?, ?, ?)")
_batch = BatchStatement(consistency_level=0)
for user in _add_users:
print(user)
_batch.add(_prepare_insert, [ user['userid'], user['first_name'], user['last_name'], user['emails'] ])
# _batch.add(_prepare_insert, [ user['userid'], user['first_name'], user['emails'] ])
_session.execute(_batch)
except:
print('EXCEPT insert: {}({})'.format(sys.exc_info()[0], sys.exc_info()[1]))
else:
_session.shutdown()
def __insert(self, models, if_not_exists=None):
assert models, 'You can insert nothing, what good would that do?'
serial_consistency_level = None
if if_not_exists:
serial_consistency_level = ConsistencyLevel.SERIAL
batch = BatchStatement(serial_consistency_level=serial_consistency_level)
for model in models:
self.__validate_model(model)
model._pre_put_hook()
metadata = self._get_table_metadata(model.table())
fields = self.denormalize(model)
fields.update(self.construct_primary_key(model, metadata))
cql_qry = CassandraQuery(model.query()).insert(fields)
if if_not_exists:
cql_qry.if_not_exists()
batch.add(cql_qry.statement, parameters=cql_qry.condition_values)
self._execute_batch(batch)
for model in models:
model._post_put_hook()
edges = self.find_edges(model)
if edges:
self.set_edges_for_model(model, edges)
if len(models) == 1:
return models[0]
return models
def test_unicode(self):
"""
Test to validate that unicode query strings are handled appropriately by various query types
@since 3.0.0
@jira_ticket PYTHON-334
@expected_result no unicode exceptions are thrown
@test_category query
"""
unicode_text = u"Fran\u00E7ois"
batch = BatchStatement(BatchType.LOGGED)
batch.add(
u"INSERT INTO {0}.{1} (k, v) VALUES (%s, %s)".format(self.keyspace_name, self.function_table_name),
(0, unicode_text),
)
self.session.execute(batch)
self.session.execute(
u"INSERT INTO {0}.{1} (k, v) VALUES (%s, %s)".format(self.keyspace_name, self.function_table_name),
(0, unicode_text),
)
prepared = self.session.prepare(
u"INSERT INTO {0}.{1} (k, v) VALUES (?, ?)".format(self.keyspace_name, self.function_table_name)
)
bound = prepared.bind((1, unicode_text))
self.session.execute(bound)
def save_eod_data_to_db(data):
"""
Saves Data to Cassandra in row batches
"""
logger.info('Received Data: {}'.format(data['ticker']))
q = """
INSERT INTO historical_data (exchange, ticker, eoddate, open, close, high, low, volume)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s);
"""
session = cluster.connect('test')
df = data['df'].dropna()
for df in np.array_split(df, len(df) / 200):
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM)
for eoddate, row in df.iterrows():
batch.add(q, [data['exchange'],
data['ticker'],
str(eoddate),
row['Open'],
row['Close'],
row['High'],
row['Low'],
int(row['Volume'])]
)
session.execute(batch)
logger.info('Done')
def insert_data():
# Example showing how to read data from a csv and insert into Cassandra using a prepared statement
insert_statement = session.prepare("INSERT INTO sensor_data.raw (device, sensor, time, metric) VALUES (?,?,?,?)")
f = open("rows_input.csv")
rows = f.read().splitlines()
f.close()
rows_inserted = 0
# Example showing how to use Cassandra batched insert
batch_insert = BatchStatement(consistency_level=ConsistencyLevel.QUORUM)
for row in rows:
data = row.split(",")
values = [data[0], data[1], datetime.datetime.strptime(data[2], "%Y-%m-%d %H:%M:%S"), float(data[3])]
insert = insert_statement.bind(values)
batch_insert.add(insert)
rows_inserted += 1
if rows_inserted % BATCH_SIZE == 0:
session.execute_async(batch_insert)
print "Inserted %d rows" % rows_inserted
return rows_inserted
def insert(self, events):
if not events:
return
batch_stmt = BatchStatement(batch_type=BatchType.UNLOGGED,
consistency_level=ConsistencyLevel.QUORUM)
shard_idx = {}
for _id, event in events:
shard_time = round_down(event[TIMESTAMP_FIELD], self.width)
shard = shard_idx.get(shard_time,
random.randint(0, self.shards - 1))
# Insert to index.
try:
self.index_cache.get((shard_time, shard))
except KeyError:
batch_stmt.add(BoundStatement(self.namespace.INDEX_INSERT_STMT,
routing_key=self.stream,
consistency_level=ConsistencyLevel.QUORUM)
.bind((self.stream, shard_time, self.width, shard)))
self.index_cache.set((shard_time, shard), None)
# Insert to stream.
shard_key = StreamShard.get_key(self.stream, shard_time, shard)
batch_stmt.add(BoundStatement(self.namespace.INSERT_STMT,
routing_key=shard_key,
consistency_level=ConsistencyLevel.QUORUM)
.bind((shard_key,
_id,
marshal.dumps(event))))
shard_idx[shard_time] = (shard + 1) % self.shards # Round robin.
self.session.execute(batch_stmt)
def save_result(self, check_uuid: str, trigger: dict,
scheduled_time: object,
execution_time: object,
insertion_time: object,
alert_sent: bool,
service_id: str,
**kwargs):
"""Save check result.
:param check_uuid: check uuid
:param dict trigger: trigger definition
:param scheduled_time: time when check was scheduled
:param execution_time: time when worker finished execution
:param insertion_time: time when check was analyzed by alerter
:param alert_sent: indicates when alert was sent to AER
:param service_id: service id
"""
batch = BatchStatement()
args = (
UUID(check_uuid), UUID(trigger['uuid']),
scheduled_time, execution_time, insertion_time,
alert_sent,
trigger['result']['status'],
trigger['result']['message'],
service_id,
kwargs
)
batch.add(self._save_query_stmt, args)
batch.add(self._save_service_query_stmt, args)
self._session.execute(batch)
def record_reads(self, app, txid, group_keys):
""" Keep track of which entity groups were read in a transaction.
Args:
app: A string specifying an application ID.
txid: An integer specifying a transaction ID.
group_keys: An iterable containing Reference objects.
"""
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM,
retry_policy=BASIC_RETRIES)
insert = self.session.prepare("""
INSERT INTO transactions (txid_hash, operation, namespace, path)
VALUES (?, ?, ?, ?)
USING TTL {ttl}
""".format(ttl=dbconstants.MAX_TX_DURATION * 2))
for group_key in group_keys:
if not isinstance(group_key, entity_pb.Reference):
group_key = entity_pb.Reference(group_key)
args = (tx_partition(app, txid),
TxnActions.GET,
group_key.name_space(),
bytearray(group_key.path().Encode()))
batch.add(insert, args)
try:
yield self.tornado_cassandra.execute(batch)
except dbconstants.TRANSIENT_CASSANDRA_ERRORS:
message = 'Exception while recording reads in a transaction'
logger.exception(message)
raise AppScaleDBConnectionError(message)
def delete_entities_tx(self, app, txid, entity_keys):
""" Update transaction metadata with new delete operations.
Args:
app: A string containing an application ID.
txid: An integer specifying the transaction ID.
entity_keys: A list of entity keys that will be deleted upon commit.
"""
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM,
retry_policy=BASIC_RETRIES)
insert = self.session.prepare("""
INSERT INTO transactions (txid_hash, operation, namespace, path, entity)
VALUES (?, ?, ?, ?, ?)
USING TTL {ttl}
""".format(ttl=dbconstants.MAX_TX_DURATION * 2))
for key in entity_keys:
# The None value overwrites previous puts.
args = (tx_partition(app, txid),
TxnActions.MUTATE,
key.name_space(),
bytearray(key.path().Encode()),
None)
batch.add(insert, args)
try:
yield self.tornado_cassandra.execute(batch)
except dbconstants.TRANSIENT_CASSANDRA_ERRORS:
message = 'Exception while deleting entities in a transaction'
logger.exception(message)
raise AppScaleDBConnectionError(message)
def update_gene_summary_w_cancer_census(session, genes):
update_qry = "UPDATE gene_summary SET in_cosmic_census = ? "
update_qry += " WHERE gene = ? and chrom = ?"
query = session.prepare(update_qry)
batch = BatchStatement()
for gene in genes:
batch.add(query, gene)
session.execute(batch)
def test_rk_from_simple(self):
"""
batch routing key is inherited from SimpleStatement
"""
batch = BatchStatement()
batch.add(self.simple_statement)
self.assertIsNotNone(batch.routing_key)
self.assertEqual(batch.routing_key, self.simple_statement.routing_key)
def test_simple_statements(self):
batch = BatchStatement(BatchType.LOGGED)
for i in range(10):
batch.add(SimpleStatement("INSERT INTO test3rf.test (k, v) VALUES (%s, %s)"), (i, i))
self.session.execute(batch)
self.session.execute_async(batch).result()
self.confirm_results()
def copy_dynamic_table(table):
c_ft = pycassa.ColumnFamily(pool, table)
table_rows = (c_ft.get_range(include_ttl=True))
command = "INSERT INTO {0} ("
table_columns = []
values = ''
keyCount = 0
for t in table_list:
if(t.table_name == table):
table_columns = t.columns
for column in t.columns:
if keyCount == 0:
command += column.name
values = ') VALUES (?'
else:
command += ', ' + column.name
values += ', ?'
if (column.key == True):
keyCount += 1
break
command += values + ') USING TTL ?'
insert_flow = client.session.prepare(command.format(table))
batch = BatchStatement()
num_rows_in_batch = 0
rowKey = ''
for rowKey,columns in table_rows:
key_value = []
rowKey_size = 0
if isinstance(rowKey, tuple):
for key in range(0, len(list(rowKey))):
key_value.append(_convert_to_cql_data(table_columns, rowKey_size, rowKey, rowKey_size))
rowKey_size += 1
if(t.is_index_table == True):
key_value.append(random.randrange(0, 16))
else:
key_value.append(_convert_to_cql_data(table_columns, rowKey_size, rowKey, rowKey_size))
rowKey_size += 1
for key in columns.keys():
value, ttl = columns.get(key)
jsonValue = value
if is_json(value) != True:
jsonValue = json.dumps(value, ensure_ascii=False)
column_value = copy.deepcopy(key_value)
num_rows_in_batch += 1
if(num_rows_in_batch % args.max_batch_entry_count == 0):
num_rows_in_batch = 0
client.load_data(batch)
batch = BatchStatement()
for col_num in range(rowKey_size, len(table_columns)-1):
column_value.append(_convert_to_cql_data(table_columns, col_num, key, col_num-rowKey_size))
column_value.append(jsonValue)
column_value.append(ttl if ttl else 7200)
batch.add(insert_flow, tuple(column_value))
if(rowKey != ''):
client.load_data(batch)
def do_update(cluster, target_db, schema_file, record_size, start_record, batch_size, insert_percentage, delay, batch_count, replication_factor=3, suppress_output=False):
record_size = int(record_size)
start_record = int(start_record)
batch_size = int(batch_size)
insert_percentage = int(insert_percentage)
delay = float(delay) / 1000
batch_count = int(batch_count)
nr_batch = 0
random.seed(1)
ks_name, cf_name = getKSCFNames(target_db)
if ks_name == None or cf_name == None:
return
createKeyspace(cluster, ks_name, replication_factor=replication_factor)
createTable(cluster, ks_name, cf_name, schema_file)
ts = TestSchema(cluster, ks_name, cf_name)
ts.getSchema()
while True:
if ts.counter_table:
batch = BatchStatement(batch_type = BatchType.COUNTER)
else:
batch = BatchStatement()
stat_str = ''
for i in range(batch_size):
if start_record <= 0 or random.randrange(100) <= insert_percentage:
# insert case
record_num = start_record
query = ts.getInsertQuerywithRandomData(record_num, record_size)
stat_str += 'I(%d) ' % record_num
else:
record_num = random.randrange(0, start_record)
if random.randrange(100) <= 70: # 70% update
if not ts.counter_table:
query = ts.getUpdateQuery(record_num)
else:
query = ts.getInsertQuerywithRandomData(record_num, 0)
stat_str += 'U(%d) ' % record_num
else: # 30% deletion
query = ts.getDeleteQuery(record_num)
stat_str += 'D(%d) ' % record_num
if not suppress_output:
report(stat_str)
batch.add(query)
start_record += 1
#print stat_str
cluster.session.execute(batch)
nr_batch += 1
if nr_batch == batch_count:
if batch_count >= 0:
break
time.sleep(delay)
def test_rk_from_bound(self):
"""
batch routing key is inherited from BoundStatement
"""
bound = self.prepared.bind((1, None))
batch = BatchStatement()
batch.add(bound)
self.assertIsNotNone(batch.routing_key)
self.assertEqual(batch.routing_key, bound.routing_key)
def sendPartition(iter):
cluster = Cluster(['hdp-master','hdp-slave1','hdp-slave2','hdp-slave3'])
session = cluster.connect('coursera')
insert = session.prepare("INSERT INTO destination_rank_by_airport (airport, destination_rank) VALUES (?, ?)")
batch = BatchStatement()
for record in iter:
batch.add(insert, record)
session.execute(batch)
cluster.shutdown()
def _assert_invalid_request(self, session, insert_cql, value):
""" Perform two executions of the supplied statement, as a
single statement and again as part of a batch
"""
prepared = session.prepare(insert_cql)
self._execute_and_fail(lambda: session.execute(prepared, [value]), insert_cql)
batch = BatchStatement()
batch.add(prepared, [value])
self._execute_and_fail(lambda: session.execute(batch), insert_cql)
def test_clear_empty(self):
batch = BatchStatement()
batch.clear()
self.assertFalse(batch._statements_and_parameters)
self.assertIsNone(batch.keyspace)
self.assertIsNone(batch.routing_key)
self.assertFalse(batch.custom_payload)
batch.add('something')
def sendPartition(iter):
cluster = Cluster(['hdp-master','hdp-slave1','hdp-slave2','hdp-slave3'])
session = cluster.connect('coursera')
insert = session.prepare("INSERT INTO source_destination_mean_delay (source, destination, mean_arrival_delay) VALUES (?, ?, ?)")
batch = BatchStatement()
for record in iter:
batch.add(insert, record)
session.execute(batch)
cluster.shutdown()
def test_bound_statements(self):
prepared = self.session.prepare("INSERT INTO test3rf.test (k, v) VALUES (?, ?)")
batch = BatchStatement(BatchType.LOGGED)
for i in range(10):
batch.add(prepared.bind((i, i)))
self.session.execute(batch)
self.session.execute_async(batch).result()
self.confirm_results()
def read_callback(rows):
global w
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM, batch_type=BatchType.UNLOGGED)
for row in rows:
if row.ttl_value:
batch.add(insert_ttl, (row.key, row.column1, row.value, row.ttl_value))
else:
batch.add(insert, (row.key, row.column1, row.value))
w = dst_session.execute_async(batch)
w.add_callbacks(callback=write_callback, callback_kwargs={'rows': len(rows)}, errback=write_errback)
def test_rk_from_simple(self):
"""
batch routing key is inherited from SimpleStatement
"""
self.cluster = Cluster(protocol_version=PROTOCOL_VERSION)
self.session = self.cluster.connect()
batch = BatchStatement()
batch.add(self.simple_statement)
self.assertIsNotNone(batch.routing_key)
self.assertEqual(batch.routing_key, self.simple_statement.routing_key)
def receive_data():
users_to_insert = request.get_json()
insert_user = db().prepare("INSERT INTO user_accel_data_test (user_id, timestamp, x, y, z) VALUES (?, ?, ?, ?, ?)")
batch = BatchStatement(consistency_level=ConsistencyLevel.ANY)
for i in users_to_insert:
batch.add(insert_user, (str(i['user_id']), str(i['timestamp']), i['x'], i['y'], i['z']))
db().execute(batch)
return Response(json.dumps(users_to_insert), mimetype='application/json')
def insert(cls, params):
idx_start, idx_end = params
batch_size = 25
batch_stmt = BatchStatement()
for index in range(idx_start, idx_end, batch_size):
curr_batch_size = min(batch_size, idx_end - index)
for i in range(0, curr_batch_size):
block = cls.chain[index + i]
block_tx = [block.height, [tx_stats(x) for x in block.txes]]
batch_stmt.add(cls.prepared_stmt, block_tx)
try:
cls.session.execute(batch_stmt)
except Exception as e:
# ingest single blocks batch ingest fails
# (batch too large error)
print(e)
for i in range(0, curr_batch_size):
while True:
try:
block = cls.chain[index + i]
block_tx = [
block.height,
[tx_stats(x) for x in block.txes]
]
cls.session.execute(cls.prepared_stmt, block_tx)
except Exception as e:
print(e)
continue
break
batch_stmt.clear()
with cls.counter.get_lock():
cls.counter.value += curr_batch_size
print('#blocks {:,.0f}'.format(cls.counter.value), end='\r')
def main():
row_count = 100000
max_insert = 10
log.info('truncate table')
session.execute('truncate ooi.vel3d_k_wfp_instrument')
log.info('done truncating')
log.info('generating row data')
rows = create_rows(row_count)
now = time.time()
batches = []
batch = BatchStatement()
for i, row in enumerate(rows):
if (i + 1) % max_insert == 0:
batches.append((batch, []))
batch = BatchStatement()
batch.add(insert, row)
batches.append((batch, []))
log.info('inserting')
execute_concurrent(session, batches, concurrency=50)
log.info('%d rows: %7.2f sec elapsed', row_count, time.time() - now)
def insertGroupOfObjects(self, objetos):
insert_user = self.cassandra.session.prepare(
"INSERT INTO inscritos (id, ANO_NASCIMENTO, PESO, ALTURA, CABECA, CALCADO, CINTURA, RELIGIAO, MUN_NASCIMENTO, UF_NASCIMENTO, PAIS_NASCIMENTO, ESTADO_CIVIL, SEXO, ESCOLARIDADE, VINCULACAO_ANO, DISPENSA, ZONA_RESIDENCIAL, MUN_RESIDENCIA, UF_RESIDENCIA, PAIS_RESIDENCIA, JSM, MUN_JSM, UF_JSM)VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
)
objetos_parts = [objetos[i:i + 50] for i in range(0, len(objetos), 50)]
for parts_objects in objetos_parts:
batch = BatchStatement()
for objeto in parts_objects:
id_ = uuid.uuid4().hex
batch.add(
insert_user,
(id_, objeto['ANO_NASCIMENTO'], objeto['PESO'],
objeto['ALTURA'], objeto['CABECA'], objeto['CALCADO'],
objeto['CINTURA'], objeto['RELIGIAO'],
objeto['MUN_NASCIMENTO'], objeto['UF_NASCIMENTO'],
objeto['PAIS_NASCIMENTO'], objeto['ESTADO_CIVIL'],
objeto['SEXO'], objeto['ESCOLARIDADE'],
objeto['VINCULACAO_ANO'], objeto['DISPENSA'],
objeto['ZONA_RESIDENCIAL'], objeto['MUN_RESIDENCIA'],
objeto['UF_RESIDENCIA'], objeto['PAIS_RESIDENCIA'],
objeto['JSM'], objeto['MUN_JSM'], objeto['UF_JSM']))
self.cassandra.session.execute(batch)
def add_transactional_tasks(self, app, txid, tasks, service_id, version_id):
""" Add tasks to be enqueued upon the completion of a transaction.
Args:
app: A string specifying an application ID.
txid: An integer specifying a transaction ID.
tasks: A list of TaskQueueAddRequest objects.
service_id: A string specifying the client's service ID.
version_id: A string specifying the client's version ID.
"""
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM,
retry_policy=BASIC_RETRIES)
query_str = (
'INSERT INTO transactions (txid_hash, operation, namespace, path, task) '
'VALUES (?, ?, ?, ?, ?) '
'USING TTL {ttl}'
).format(ttl=dbconstants.MAX_TX_DURATION * 2)
insert = self.session.prepare(query_str)
for task in tasks:
task.clear_transaction()
# The path for the task entry doesn't matter as long as it's unique.
path = bytearray(str(uuid.uuid4()))
task_payload = '_'.join([service_id, version_id, task.Encode()])
args = (tx_partition(app, txid),
TxnActions.ENQUEUE_TASK,
'',
path,
task_payload)
batch.add(insert, args)
try:
yield self.tornado_cassandra.execute(batch)
except dbconstants.TRANSIENT_CASSANDRA_ERRORS:
message = 'Exception while adding tasks in a transaction'
logging.exception(message)
raise AppScaleDBConnectionError(message)
def save_async(self, trades):
"""
:type entry_type: str
:type quote: RTQuote
"""
query = \
"""
INSERT INTO trades
({})
VALUES ({})
""".format(','.join(FIELDS_Trades),
','.join("%s" for _ in FIELDS_Trades))
batch_statement = BatchStatement()
for i, trade in trades.iterrows():
data = tuple(trade[field] for field in FIELDS_Trades)
batch_statement.add(query,data)
if len(batch_statement)>= MAX_BATCH_SIZE:
get_async_manager().execute_async(self._session,batch_statement)
batch_statement = BatchStatement()
if len(batch_statement) > 0:
get_async_manager().execute_async(self._session,batch_statement)
def save_to_db(db):
'''
Save raw data to cassandra database
'''
date = datetime.datetime.now().strftime("%B %d, %Y")
timestamp = datetime.datetime.now()
Record = int(db[0])
message = np.array_str(db)
print "== begin saving to db =="
cassandra_cluster = Cluster(config.cass_cluster_IP)
cassandra_session = cassandra_cluster.connect('ecg')
flow = cassandra_session.prepare(
'''INSERT INTO ecg_stream(Record, day, ts, message) VALUES (?,?,?,?)'''
)
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM)
try:
batch.add(flow, (Record, date, timestamp, message))
except:
pass
cassandra_session.execute(batch)
cassandra_cluster.shutdown()
def test_inherit_first_rk_simple_statement(self):
"""
compound batch inherits the first routing key of the first added statement (Simplestatement is first)
"""
bound = self.prepared.bind((1, None))
batch = BatchStatement()
batch.add("ss with no rk")
batch.add(self.simple_statement)
batch.add(bound)
for i in range(10):
batch.add(self.prepared, (i, i))
self.assertIsNotNone(batch.routing_key)
self.assertEqual(batch.routing_key, self.simple_statement.routing_key)
def main(inputs, key_space, table):
cluster = Cluster(['199.60.17.188', '199.60.17.216'])
session = cluster.connect(key_space)
session.execute("""
CREATE TABLE IF NOT EXISTS nasalogs (
host TEXT,
datetime TIMESTAMP,
path TEXT,
bytes INT,
recId UUID,
PRIMARY KEY (host,recId)
)
""")
session.execute("""TRUNCATE nasalogs;""")
insert_log = session.prepare(
"INSERT INTO " + table +
" (host,datetime,path,bytes,recId) VALUES (?,?,?,?,?)")
batch = BatchStatement(consistency_level=ConsistencyLevel.ONE)
c = 0
for g_file in os.listdir(inputs):
with gzip.open(os.path.join(inputs, g_file), 'rt',
encoding='utf-8') as logfile:
for line in logfile:
w = get_words(line)
if len(w) > 4:
c += 1
batch.add(
insert_log,
(w[1],
datetime.datetime.strptime(w[2], '%d/%b/%Y:%H:%M:%S'),
w[3], int(w[4]), uid()))
if (c == 400):
session.execute(batch)
batch.clear()
c = 0
session.execute(batch)
cluster.shutdown()
def test_custom_query_batching(self):
"""
Test to validate that custom payloads work with batch queries
creates a batch query and ensures that custom payloads are passed to C*. A custom
query provider is used with C* so we can validate that same custom payloads are sent back
with the results
@since 2.6
@jira_ticket PYTHON-280
@expected_result valid custom payloads should be sent and received
@test_category queries:custom_payload
"""
# Construct Batch Statement
batch = BatchStatement(BatchType.LOGGED)
for i in range(10):
batch.add(SimpleStatement("INSERT INTO test3rf.test (k, v) VALUES (%s, %s)"), (i, i))
# Validate that various types of custom payloads are sent and received okay
self.validate_various_custom_payloads(statement=batch)
def sendCassandraVenueVisit(iter):
print("send Venue Visit to cassandra")
cluster = Cluster(['52.88.251.94'],
control_connection_timeout=None,
port=9042)
session = cluster.connect()
session.execute('USE ' + "playground") # provide keyspace
# session.execute("TRUNCATE venueVisit")
insert_statement = session.prepare(
"INSERT INTO venueVisit (venue_id, visit) VALUES (?,?)")
count = 0
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM)
for record in iter:
batch.add(insert_statement, (record[1][0], record[1][1]))
# count += 1
# if count % 200== 0:
# session.execute(batch)ii
# batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM)
print "saved"
session.execute(batch)
session.shutdown()
def save_async(self, quotes):
"""
:type entry_type: str
:type quote: RTQuote
"""
batch_statement = BatchStatement()
for quote in quotes:
query = \
"""
INSERT INTO quotes
({})
VALUES ({})
""".format(','.join(quote.keys()),
','.join("%s" for _ in quote.keys()))
data = tuple(quote[field] for field in quote.keys())
batch_statement.add(query,data)
if len(batch_statement)>= MAX_BATCH_SIZE:
get_async_manager().execute_async(self._session,batch_statement)
batch_statement = BatchStatement()
if len(batch_statement) > 0:
get_async_manager().execute_async(self._session,batch_statement)
def save_to_db(db):
'''
Save raw data to cassandra database
'''
date = datetime.datetime.now().strftime("%B %d, %Y")
temp = []
for value in db[1:-3]:
tu = (int(db[0]),date, datetime.datetime.now(), value)
temp.append(tu)
print "== begin save tbl2 ======"
cassandra_cluster = Cluster(config.cass_cluster_IP)
cassandra_session = cassandra_cluster.connect('ecg')
flow = cassandra_session.prepare('''INSERT INTO ecg_stream(Record, day, ts, message) VALUES (?,?,?,?)''')
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM)
for d in temp:
try:
batch.add(flow, d)
except:
pass
cassandra_session.execute(batch)
cassandra_cluster.shutdown()
print "== end save tbl2 ======"
def insert_newlog(log_file, table_name):
count = 1
insert_query = session.prepare(
"INSERT INTO " + table_name +
" (host, id, datetime, path, bytes) VALUES (?, uuid(), ?, ?, ?)")
batch = BatchStatement()
for line in log_file:
values = log_dissemble.split(line)
if len(
values
) >= 4: # Only consider lines which can be split as host, dtime, path, num_bytes
host = values[1]
dtime = datetime.datetime.strptime(values[2], '%d/%b/%Y:%H:%M:%S')
path = values[3]
num_bytes = int(values[4])
count += 1
batch.add(insert_query, (host, dtime, path, num_bytes))
if count == 300:
session.execute(batch)
batch.clear()
count = 1
session.execute(batch)
batch.clear()
def sendCassandrastate(iter):
print("send to cassandra")
cluster = Cluster(
['35.164.226.10', '52.24.193.4', '52.24.193.4', '54.245.7.62'])
session = cluster.connect()
session.execute('USE ' + "Energy_consumption")
insert_statement = session.prepare(
"INSERT INTO statedata (Time, State, Ecoll, Efacility, Efan, Gfacility, Eheat, Gheat, Einterequip, Ginterequip, Einterlight, Gwater, Etotal, Gtotal) VALUES (?, ?,?,?,?,?,?,?,?,?,?,?,?,?)"
)
count = 0
# batch insert into cassandra database
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM)
for record in iter:
# create new columns when writing data to cassandra, it saves much more time than doing a map job in the streaming process then save to cassandra
num11 = record[1][3] + record[1][4] + record[1][5] + record[1][
7] + record[1][9] + record[1][11]
num12 = record[1][6] + record[1][8] + record[1][10] + record[1][
12] + record[1][11]
batch.add(insert_statement,
(record[1][13], record[1][2], record[1][3], record[1][4],
record[1][5], record[1][6], record[1][7], record[1][8],
record[1][9], record[1][10], record[1][11], record[1][12],
num11, num12))
#split the batch, so that the batch will not exceed the size limit
count += 1
if count % 200 == 0:
session.execute(batch)
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM)
print "saved"
# send the batch that is less than 500
session.execute(batch)
session.shutdown()
def batch_insert(cql: str, cols: tuple, data: pd.DataFrame, size: int,
session: cassandra.cluster.Session):
"""
Given an Apache Cassandra session, use the provided CQL statement to insert via batches.
http://datastax.github.io/python-driver/api/cassandra/query.html#module-cassandra.query
:param cql: CQL insert statement
:param cols: Columns in which data is inserted
:param data: Data to be inserted
:param size: Size of batch
:param session: Apache Cassandra session
"""
query = session.prepare(cql)
# convert NaNs to None and subset input data
converted_data = convert_to_none(data[cols])
splits = make_split(converted_data, size)
# generate batches
print(
f"Starting batch insert for {converted_data.shape[0]} rows in {len(splits)} batches of size {size}"
)
for ix, split in enumerate(splits):
try:
batch = BatchStatement()
# add rows of splits to batch
for _, row in split.iterrows():
batch.add(query, list(row))
session.execute(batch)
print(f"Inserted {len(split)} rows of data")
except Exception as e:
print(f"Insert for batch {ix} failed: {e}")
print("Batch insert finished")
def cassandra_tbl2(rdd):
'''
function used to save to cassandra database tbl2
CREATE TABLE IF NOT EXISTS intrawindow(
underlying_symbol text, quote_datetime timestamp,
option_type text, cum_delta float,
PRIMARY KEY((underlying_symbol), quote_datetime, option_type, cum_delta))
'''
spark = getSparkSessionInstance(rdd.context.getConf())
rowRdd = rdd.map(lambda w: Row(underlying_symbol=w[0][0],
option_type=w[0][1],
quote_datetime=w[0][2],
total_Delta=float(w[1])))
complete_df = spark.createDataFrame(rowRdd)
print "== begin save tbl2 ======"
w1 = complete_df.groupBy("underlying_symbol", "option_type",
F.window("quote_datetime", "60 seconds")).agg(
F.sum("total_Delta").alias('cum_total_Delta'))
w1_list = [(row.underlying_symbol, row.option_type, row.window,
row.cum_total_Delta) for row in w1.collect()]
cassandra_cluster = Cluster(config_pool.cass_cluster_IP)
cassandra_session = cassandra_cluster.connect('demo1')
insert_intrawindow_flow = cassandra_session.prepare(
'''INSERT INTO intrawindow (underlying_symbol, quote_datetime, option_type, cum_delta) VALUES (?,?,?,?)'''
)
batch = BatchStatement(consistency_level=ConsistencyLevel.ANY)
for d in w1_list:
try:
batch.add(insert_intrawindow_flow,
(str(d[0]), d[2][0], str(d[1]), float(d[3])))
except:
pass
cassandra_session.execute(batch)
cassandra_cluster.shutdown()
print "== end save tbl2 ======"
def test_batch_statement(self):
session, tracer = self._traced_session()
batch = BatchStatement()
batch.add(
SimpleStatement(
"INSERT INTO test.person_write (name, age, description) VALUES (%s, %s, %s)"
),
("Joe", 1, "a"),
)
batch.add(
SimpleStatement(
"INSERT INTO test.person_write (name, age, description) VALUES (%s, %s, %s)"
),
("Jane", 2, "b"),
)
session.execute(batch)
spans = tracer.pop()
assert len(spans) == 1
s = spans[0]
assert s.resource == "BatchStatement"
assert s.get_metric("cassandra.batch_size") == 2
assert "test.person" in s.get_tag("cassandra.query")
def cassandraBDProcess():
print('START...')
for i in range(1, 226):
#Connect to Cassandra
objCC = CassandraConnection()
cloud_config = {
'secure_connect_bundle': pathToHere + '\\secure-connect-dbtest.zip'
}
auth_provider = PlainTextAuthProvider(objCC.cc_user_test,
objCC.cc_pwd_test)
cluster = Cluster(cloud=cloud_config, auth_provider=auth_provider)
session = cluster.connect()
session.default_timeout = 20
#Start of the batch
batchCounter = 0
batch = BatchStatement()
while batchCounter <= 100:
with open('demo.json', encoding='utf-8') as f:
json_thesis = json.load(f)
json_thesis['guid_thesis'] = str(uuid.uuid4())
json_thesis = json.dumps(json_thesis)
insertSt = "INSERT INTO test.tbthesis JSON '" + json_thesis + "';"
batch.add(SimpleStatement(insertSt))
batchCounter = batchCounter + 1
session.execute(batch)
print("Batch:", str(i))
time.sleep(6)
cluster.shutdown()
def test_batch_statement(self):
session, tracer = self._traced_session()
batch = BatchStatement()
batch.add(
SimpleStatement(
'INSERT INTO test.person_write (name, age, description) VALUES (%s, %s, %s)'
),
('Joe', 1, 'a'),
)
batch.add(
SimpleStatement(
'INSERT INTO test.person_write (name, age, description) VALUES (%s, %s, %s)'
),
('Jane', 2, 'b'),
)
session.execute(batch)
spans = tracer.pop()
assert len(spans) == 1
s = spans[0]
assert s.resource == 'BatchStatement'
assert s.get_metric('cassandra.batch_size') == 2
assert 'test.person' in s.get_tag('cassandra.query')
def Update(self, Entities:tuple, Events:tuple, MessageId=None)-> None:
EventTime = datetime.datetime.now()
batch = BatchStatement()
for Event, Entity in zip(Events,Entities):
batch.add("INSERT INTO Events (EntityType, EntityId, EventType, EventData, EventTime, Published) VALUES (%s,%s,%s,%s,%s,%s)", (Entity.EntityType, Entity.Id, Event.EventType, Event.EventJsonData(),EventTime,False))
batch.add("UPDATE Snapshots SET SnapshotData = %s, EventTime = %s WHERE EntityType=%s and EntityId=%s", [Entity.SnapshotData(),EventTime,Entity.EntityType,Entity.Id])
if MessageId != None:
batch.add("INSERT INTO MessageId (MessageId) VALUES (%s)", [MessageId])
self.session.execute(batch)
def _normal_batch(self, mutations, txid):
""" Use Cassandra's native batch statement to apply mutations atomically.
Args:
mutations: A list of dictionaries representing mutations.
txid: An integer specifying a transaction ID.
"""
self.logger.debug('Normal batch: {} mutations'.format(len(mutations)))
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM,
retry_policy=BASIC_RETRIES)
prepared_statements = {'insert': {}, 'delete': {}}
for mutation in mutations:
table = mutation['table']
if table == 'group_updates':
key = mutation['key']
insert = """
INSERT INTO group_updates (group, last_update)
VALUES (%(group)s, %(last_update)s)
USING TIMESTAMP %(timestamp)s
"""
parameters = {
'group': key,
'last_update': mutation['last_update'],
'timestamp': get_write_time(txid)
}
batch.add(insert, parameters)
continue
if mutation['operation'] == Operations.PUT:
if table not in prepared_statements['insert']:
prepared_statements['insert'][table] = self.prepare_insert(
table)
values = mutation['values']
for column in values:
batch.add(
prepared_statements['insert'][table],
(bytearray(mutation['key']), column,
bytearray(values[column]), get_write_time(txid)))
elif mutation['operation'] == Operations.DELETE:
if table not in prepared_statements['delete']:
prepared_statements['delete'][table] = self.prepare_delete(
table)
batch.add(prepared_statements['delete'][table],
(get_write_time(txid), bytearray(mutation['key'])))
try:
self.session.execute(batch)
except dbconstants.TRANSIENT_CASSANDRA_ERRORS:
message = 'Exception during batch_mutate'
logging.exception(message)
raise AppScaleDBConnectionError(message)
def add():
if request.method == 'POST':
new_data = {k: v for k, v in request.form.items()}
#If the user leaves a field blank
if new_data['title'] == '' or new_data['author'] == '' or new_data[
'genre'] == '' or new_data['description'] == '':
return render_template('add.html', alert="required")
else:
id = uuid.uuid4()
batch = BatchStatement()
insert_statement = "INSERT INTO " + table_name + "(id, property, value) values(" + str(
id) + ", %s, %s)"
batch.add(insert_statement, ('title', new_data['title']))
batch.add(insert_statement, ('author', new_data['author']))
batch.add(insert_statement, ('genre', new_data['genre']))
batch.add(insert_statement,
('description', new_data['description']))
session.execute(batch)
return render_template('add.html', alert="success")
else:
return render_template('add.html', alert="")
def insert_pixels(cfg, detections):
with cluster(cfg) as c:
s = session(cfg, c)
st = 'INSERT INTO {keyspace}.pixel (cx, cy, px, py, mask) VALUES (?, ?, ?, ?, ?)'.format(
keyspace=cfg['cassandra_keyspace'])
stmt = s.prepare(st)
chunks = partition_all(cfg['cassandra_batch_size'], detections)
batches = []
for chunk in chunks:
batch = BatchStatement(batch_type=BatchType.UNLOGGED)
[
batch.add(stmt,
[c['cx'], c['cy'], c['px'], c['py'], c['mask']])
for c in chunk
]
batches.append(batch)
return [s.execute(b) for b in batches]
def save_batch(self, payload: (str or List[str])) -> int:
"""
Saving batch of events.
:param payload: new line separated JSONs for events or list of it
:return: Number of batched statements
"""
assert payload is not None
if isinstance(payload, str):
payload = payload.split(sep='\n')
batch_sessions = BatchStatement()
pairs = []
for js_str in payload:
if len(js_str) == 0:
continue
js = json.loads(js_str)
session_id = UUID(js['session_id'])
player_id = UUID(js['player_id'])
ts = str2date(js['ts'])
pairs.append((session_id, player_id))
if js['event'] == 'start':
batch_sessions.add(self.insert_start,
(session_id, player_id, js['country'], ts))
else:
batch_sessions.add(self.insert_end,
(session_id, player_id, ts))
self.session.execute(batch_sessions)
batch_completes = BatchStatement()
for pair in pairs:
for row in self.session.execute(
self.select_complete_in_last_update, pair):
batch_completes.add(
self.insert_complete,
(row.player_id, row.session_id, row.finish))
self.session.execute(batch_completes)
num_statements = len(batch_sessions) + len(batch_completes)
return num_statements
def batch_query_by_token(self, bound_stmt, query_map):
token = self.metadata.token_map.token_class.from_key(bound_stmt.routing_key)
queue = query_map.get(token, None)
if not queue:
queue = []
batch = BatchStatement(BatchType.UNLOGGED)
batch.add(bound_stmt)
queue.append((batch, Counter(1)))
query_map[token] = queue
else:
(batch, counter) = queue[-1]
if counter.value() < self.batch_limit:
batch.add(bound_stmt)
counter.increment()
else:
batch = BatchStatement(BatchType.UNLOGGED)
batch.add(bound_stmt)
queue.append((batch, Counter(1)))
def Create(self, Entities: tuple, Events: tuple, MessageId=None) -> None:
batch = BatchStatement()
EventTime = datetime.datetime.now()
for Event, Entity in zip(Events, Entities):
batch.add(
"INSERT INTO Events (EntityType, EntityId, EventType, EventData, EventTime, Published) VALUES (%s,%s,%s,%s,%s,%s)",
(Entity.EntityType, Entity.Id, Event.EventType,
Event.EventJsonData(), EventTime, False))
batch.add(
"INSERT INTO Snapshots (EntityId, EntityType, EventTime, snapshotdata, SnapshotType, SemantickLock) VALUES (%s,%s,%s,%s,%s,%s)",
[
Entity.Id, Entity.EntityType, EventTime,
Entity.SnapshotData(), Entity.Name, 'Unlock'
])
if MessageId != None:
batch.add("INSERT INTO MessageId (MessageId) VALUES (%s)",
[MessageId])
self.session.execute(batch)
def batch_query_by_replicas(self, bound_stmt, query_map):
hosts = tuple(
self.lb_policy.make_query_plan(
working_keyspace=bound_stmt.keyspace, query=bound_stmt))
queue = query_map.get(hosts, None)
if not queue:
queue = []
batch = BatchStatement(BatchType.UNLOGGED)
batch.add(bound_stmt)
queue.append((batch, Counter(1)))
query_map[hosts] = queue
else:
(batch, counter) = queue[-1]
if counter.value() < 30:
batch.add(bound_stmt)
counter.increment()
else:
batch = BatchStatement(BatchType.UNLOGGED)
batch.add(bound_stmt)
queue.append((batch, Counter(1)))
def cassandra_tbl1(raw_data):
'''
function used to save to cassandra database
CREATE TABLE optionflowstreaming3(
underlying_symbol text, quote_datetime timestamp, expiration timestamp, strike float, option_type text,
unusual text, trade_size int, total_prem float, z_score float, buy_sell text,
best_bid float, best_ask float, trade_price float, days_to_exp int, error text,
exp_bin text, delta_bin text, trade_delta float, trade_iv float, trade_condition_id int,
canceled_trade_condition_id int, PRIMARY KEY((underlying_symbol), total_prem, expiration, quote_datetime))
'''
print "========begine to save tble 1========"
cassandra_cluster = Cluster(config.Config().cass_cluster_IP)
cassandra_session = cassandra_cluster.connect('demo1')
insert_option_flow = cassandra_session.prepare(
'''INSERT INTO optionflowstreaming3 (underlying_symbol, quote_datetime, expiration, strike, option_type,
unusual, trade_size, total_prem, z_score, buy_sell,
best_bid, best_ask, trade_price, days_to_exp, error,
exp_bin, delta_bin, trade_delta, trade_iv, trade_condition_id, canceled_trade_condition_id)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
''')
batch = BatchStatement(consistency_level=ConsistencyLevel.ANY)
for d in raw_data:
d = ast.literal_eval(json.dumps(d))
try:
quote_datetime_t = datetime.datetime.strptime(
d['quote_datetime'], "%Y-%m-%d %I:%M:%S.%f")
except:
quote_datetime_t = datetime.datetime(2050, 9, 29)
try:
expiration_t = datetime.datetime.strptime(d['expiration'],
"%Y-%m-%d")
except:
expiration_t = datetime.datetime(2050, 9, 29)
try:
strike_t = float(d['strike'])
except:
strike_t = 0.001
try:
trade_size_t = int(float(d['trade_size']))
except:
trade_size_t = 0
try:
total_prem_t = float(d['total_prem'])
except:
total_prem_t = 0.001
try:
z_score_t = float(d['z_score'])
except:
z_score_t = 100.0
try:
best_bid_t = float(d['best_bid'])
except:
best_bid_t = 0.001
try:
best_ask_t = float(d['best_ask'])
except:
best_ask_t = 0.001
try:
trade_price_t = float(d['trade_price'])
except:
trade_price_t = 0.001
try:
days_to_exp_t = int(float(d['days_to_exp']))
except:
days_to_exp_t = 0
try:
trade_delta_t = float(d['trade_delta'])
except:
trade_delta_t = 0.001
try:
trade_iv_t = float(d['trade_iv'])
except:
trade_iv_t = 0.001
try:
trade_condition_id_t = int(float(d['trade_condition_id']))
except:
trade_condition_id_t = 0
try:
trade_iv_t = int(float(d['trade_condition_id']))
except:
trade_iv_t = 0
try:
canceled_trade_condition_id_t = int(
float(d['canceled_trade_condition_id']))
except:
canceled_trade_condition_id_t = 0
batch.add(insert_option_flow,
(d['underlying_symbol'], quote_datetime_t, expiration_t,
strike_t, d['option_type'], d['unusual'], trade_size_t,
total_prem_t, z_score_t, d['buy_sell'], best_bid_t,
best_ask_t, trade_price_t, days_to_exp_t, d['error'],
d['exp_bin'], d['delta_bin'], trade_delta_t, trade_iv_t,
trade_condition_id_t, canceled_trade_condition_id_t))
cassandra_session.execute(batch)
cassandra_cluster.shutdown()
print "========end save tble 1========"
def test_no_parameters(self):
batch = BatchStatement(BatchType.LOGGED)
batch.add("INSERT INTO test3rf.test (k, v) VALUES (0, 0)")
batch.add("INSERT INTO test3rf.test (k, v) VALUES (1, 1)", ())
batch.add(SimpleStatement("INSERT INTO test3rf.test (k, v) VALUES (2, 2)"))
batch.add(SimpleStatement("INSERT INTO test3rf.test (k, v) VALUES (3, 3)"), ())
prepared = self.session.prepare("INSERT INTO test3rf.test (k, v) VALUES (4, 4)")
batch.add(prepared)
batch.add(prepared, ())
batch.add(prepared.bind([]))
batch.add(prepared.bind([]), ())
batch.add("INSERT INTO test3rf.test (k, v) VALUES (5, 5)", ())
batch.add("INSERT INTO test3rf.test (k, v) VALUES (6, 6)", ())
batch.add("INSERT INTO test3rf.test (k, v) VALUES (7, 7)", ())
batch.add("INSERT INTO test3rf.test (k, v) VALUES (8, 8)", ())
batch.add("INSERT INTO test3rf.test (k, v) VALUES (9, 9)", ())
self.assertRaises(ValueError, batch.add, prepared.bind([]), (1))
self.assertRaises(ValueError, batch.add, prepared.bind([]), (1, 2))
self.assertRaises(ValueError, batch.add, prepared.bind([]), (1, 2, 3))
self.session.execute(batch)
self.confirm_results()
cluster = Cluster(["127.0.0.1"],
port=9043,
compression=ENABLED_COMPRESSION)
session = cluster.connect()
session.default_consistency_level = DEFAULT_CONSISTENCY
session.execute("drop keyspace if exists benchmark_ks")
session.execute("create keyspace benchmark_ks with replication = " +
"{ 'class': 'SimpleStrategy', 'replication_factor': 1 }")
session.execute(
"create table benchmark_ks.my_table (id int primary key, name text)")
insertStatement = SimpleStatement(
"insert into benchmark_ks.my_table (id, name) values (%s, %s)")
insertBatch = BatchStatement()
for i in range(SELECT_COUNT * 2):
insertBatch.add(insertStatement, (i, "name"))
session.execute(insertBatch)
start = time.time()
selectStr = "select id, name from benchmark_ks.my_table "
limitStr = "limit %d" % SELECT_COUNT
if ENABLED_PREPARATION:
selectStatement = session.prepare(selectStr + limitStr)
else:
selectStatement = SimpleStatement(selectStr + limitStr)
for i in range(LOOP_COUNT):
rows = session.execute(selectStatement)
for row in rows:
id_, name = row.id, row.name
# print(id_, name)
print("used seconds: %s" % (time.time() - start))
