def get_prepared_insert_cql(session: Session, keyspace: str, table: str,
keys: t.List[str]) -> PreparedStatement:
positions = ["?" for _ in keys]
query = session.prepare(
f"INSERT INTO {keyspace}.{table} ({', '.join(keys)}) VALUES ({', '.join(positions)})"
)
query.consistency_level = ConsistencyLevel.LOCAL_QUORUM
return query
def async_data_insert(session: Session, keyspace: str, table: str,
measurement: Measurement) -> ResponseFuture:
prepared_statement = session.prepare(
f"INSERT INTO {keyspace}.{table} (name, timestamp, value) VALUES (?, ?, ?)"
)
# parse date into datetime instance with timezone info
timestamp = dateutil.parser.isoparse(measurement.timestamp)
return session.execute_async(
prepared_statement, (measurement.name, timestamp, measurement.value))
def write_games(session: Session, games: dict) -> None:
games_prep = session.prepare(
"insert into games (game, genre, console) values (?,?,?)")
for game, attributes in games.items():
session.execute(games_prep,
[game, attributes['genre'], attributes['console']])
print('GAMES DONE.')
def write_accounts(session: Session, accounts: dict) -> None:
games_prep = session.prepare(
"insert into accounts (streamer, language, game) values (?,?,?)")
for streamer, attributes in accounts.items():
session.execute(games_prep,
[streamer, attributes['language'], attributes['game']])
print('ACCOUNTS DONE.')
def insert_exchange_rates(session: Session, keyspace: str, table: str,
exchange_rates: pd.DataFrame) -> None:
"""Insert exchange rates into Cassandra table."""
colnames = ",".join(exchange_rates.columns)
values = ",".join(["?" for i in range(len(exchange_rates.columns))])
query = f"""INSERT INTO {keyspace}.{table}({colnames}) VALUES ({values})"""
prepared = session.prepare(query)
for _, row in exchange_rates.iterrows():
session.execute(prepared, row)
def __init__(self, session: Session) -> None:
self.session = session
self.insert_start = session.prepare("""
INSERT INTO sessions
(session_id, player_id, country, start, has_start) values
(?, ?, ?, ?, True)
""")
self.insert_end = session.prepare("""
INSERT INTO sessions
(session_id, player_id, finish, has_end) VALUES
(?, ?, ?, True)
""")
self.select_session_starts_for_last_hours = session.prepare("""
SELECT country, start FROM sessions
WHERE start > ? ALLOW FILTERING
""")
self.select_complete_in_last_update = session.prepare("""
select player_id, session_id, finish from sessions
where has_start = true and has_end = true and
session_id = ? and player_id = ? limit 1 allow filtering
""")
self.insert_complete = session.prepare("""
INSERT INTO complete_sessions
(player_id, session_id, ts) VALUES
(?, ?, ?)
""")
self.select_last_complete_sessions = session.prepare("""
SELECT session_id FROM complete_sessions
where player_id = ? limit ? ALLOW FILTERING
""")
def get_prepared_statement(
session: Session, keyspace: str, table: str
) -> PreparedStatement:
"""Build prepared CQL INSERT statement for specified table."""
cql_str = f"""SELECT column_name FROM system_schema.columns
WHERE keyspace_name = \'{keyspace}\'
AND table_name = \'{table}\';"""
result_set = session.execute(cql_str)
columns = [elem.column_name for elem in result_set._current_rows]
cql_str = build_cql_insert_stmt(columns, table)
prepared_stmt = session.prepare(cql_str)
return prepared_stmt
def insert_batch_app_history(self, rows: list, session: Session):
"""
Function to insert records into app_history table
:param rows: list of records
:param session: session object (obtained from cassandra-driver)
:return:
"""
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM)
statement = session.prepare(insert_app_history)
try:
for row in rows:
if row[13] != "":
if row[16] == "":
userId = 0
else:
userId = int(row[16])
batch.add(statement, (row[13], row[2], row[5], userId))
session.execute(batch)
except Exception as ex:
print("Error inserting records in app_history")
raise ex
def insert_batch_session(self, rows: list, session: Session):
"""
Function to insert data into cassandra in batches in session_songs
:param rows:
:param session:
:return:
"""
batch = BatchStatement(consistency_level=ConsistencyLevel.QUORUM)
statement = session.prepare(insert_session_songs)
try:
for row in rows:
if row[6] == "":
length = 0.0
else:
length = float(row[6])
batch.add(statement,
(int(row[12]), int(row[4]), row[0], row[13], length))
session.execute(batch)
except Exception as ex:
print("Error inserting data into session_songs")
raise ex
def insert_exchange_rates(session: Session, keyspace: str, table: str,
exchange_rates: pd.DataFrame) -> None:
"""Insert exchange rates into Cassandra table.
Parameters
----------
session
Cassandra session.
keyspace
Target Cassandra keyspace.
table
Cassandra table.
exchange_rates
pandas DataFrame with columns 'date', 'USD', 'EUR' etc.
"""
colnames = ",".join(exchange_rates.columns)
values = ",".join(["?" for i in range(len(exchange_rates.columns))])
query = f"""INSERT INTO {keyspace}.{table}({colnames}) VALUES ({values})"""
prepared_stmt = session.prepare(query)
for _, row in exchange_rates.iterrows():
session.execute(prepared_stmt, row)
