import json
import time
import cql
from cassandra.cluster import Cluster
from cassandra.auth import PlainTextAuthProvider
from uuid import uuid4
import os
from kafka import KafkaProducer
import requests
GCP_PROJECT_ID = 'bigdata220-final-project'
TOPIC = "meetup_rsvp"
SUBSCRIPTION = "out_meetup_rsvp"
CASS_PASS = os.environ['CASS_PASS']
auth_provider = PlainTextAuthProvider(username='******', password=CASS_PASS)
cluster = Cluster(['10.138.0.2'], auth_provider=auth_provider)
KAFKA_TOPIC = "rsvps2"
kafka_producer = KafkaProducer(bootstrap_servers='10.128.0.8:9092')
def join_with_category(message):
message = json.loads(message)
try:
group_urlname = message['group']['group_urlname']
except Exception as e:
print(e)
print("Joining on %s" % group_urlname)
response = requests.get("https://api.meetup.com/{group_urlname}".format(
from cassandra.cluster import Cluster
from cassandra.auth import PlainTextAuthProvider
import csv
import os
import sys
import pandas as pd
os_path = os.path
sys_exit = sys.exit
if __name__ == '__main__':
auth_provider = PlainTextAuthProvider(
username='******', password='******')
cluster = Cluster(auth_provider=auth_provider)
session = cluster.connect('reviews')
file_path = input('Input path to .tsv file: ')
if not os_path.isfile(file_path) or not file_path.endswith('.tsv'):
print('You must input path to .tsv file for filling db.')
sys_exit()
skipped_rows = 0
# preparing and executing my INSERT statement
strCQL_1 = "INSERT INTO reviews.reviews_by_product_id (product_id, review_id)" \
" VALUES (?,?);"
pStatement_1 = session.prepare(strCQL_1)
strCQL_2 = "INSERT INTO reviews.reviews_by_star_rating_and_product_id (product_id, star_rating, review_id)" \
from cassandra.auth import PlainTextAuthProvider
from cassandra.cluster import Cluster
from cassandra.query import BatchStatement
cluster = Cluster(auth_provider=PlainTextAuthProvider(username='******',
password='******'))
connection = cluster.connect('test')
batch_list = [
'''
UPDATE user
SET
data = {
phone: 'test',
email: 'test'
},
repo_count = 3
WHERE user_id = 1 IF EXISTS;
''', '''
UPDATE repo
SET related_link = 'asfsaf1'
WHERE repo_id = 1 AND user_id = 1 IF EXISTS;
'''
]
def execute_batch(statement_list):
batch = BatchStatement()
for q in statement_list:
batch.add(q)
connection.execute(batch)
def createSession():
cloud_config = {'secure_connect_bundle': './secure-connect-uottahack.zip'}
auth_provider = PlainTextAuthProvider('uottahack', 'c975SKEuyvXW!pU')
cluster = Cluster(cloud=cloud_config, auth_provider=auth_provider)
session = cluster.connect('uottahack')
return session
#for row in rows:
#process_row(row)
if self.future.has_more_pages:
self.future.start_fetching_next_page()
else:
self.finished_event.set()
def handle_error(self, exc):
self.error = exc
self.finished_event.set()
def process_row(user_row):
print user_row.channel, user_row.timestamp, user_row.value
channel = sys.argv[1]
auth_provider = PlainTextAuthProvider(
username='******', password='******')
cluster = Cluster(['10.150.1.69'],auth_provider=auth_provider)
session = cluster.connect()
start = time.time()
query = "SELECT * FROM production.data where channel='{}'".format(channel)
statement = SimpleStatement(query, fetch_size=10000)
future = session.execute_async(statement)
handler = PagedResultHandler(future)
handler.finished_event.wait()
if handler.error:
raise handler.error
end = time.time()
print ( end - start)
def main():
logging.basicConfig(stream=sys.stdout, level=logging.INFO)
FORMAT = '%(asctime) %(message)s'
logging.basicConfig(format=FORMAT)
ca_file_uri = "./cacert.pem"
#Check if the blob storage access credentials have been loaded as a secret volume use them
if os.path.exists('/tmp/secrets/db/db-account'):
db_account_name = open('/tmp/secrets/db/db-account').read()
db_account_key = open('/tmp/secrets/db/db-key').read()
eh_url = open().read()
eh_offset_url = open().read()
eh_account = open().read()
eh = open().read()
#Otherwise assume it is being run locally and load from environment variables
else:
db_account = os.environ['COSMOS_DB_ACCOUNT']
db_key = os.environ['COSMOS_DB_KEY']
eh_url = os.environ['EVENT_HUB_URL']
eh_offset_url = os.environ['EVENT_HUB_OFFSET_URL']
eh_account = os.environ['EVENT_HUB_ACCOUNT']
eh_key = os.environ['EVENT_HUB_KEY']
#If the test container is not loaded as an environment variable, assume a local run
#and use the configuration information in the deployment config
if 'DB_PERSONA_EDGE_TABLE' in os.environ:
db_config = None
else:
merged_config = yaml.safe_load_all(open("../cluster-deployment.yml"))
for config in merged_config:
if config['metadata']['name'] == 'azmlsi-db-config':
db_config = config['data']
#If no db config file is passed, look for the container environment variables
if db_config is None:
keyspace = os.environ['DB_KEYSPACE']
#Otherwise load db config
else:
keyspace = db_config['db-keyspace']
#Connect to the database
ssl_opts = {
'ca_certs': ca_file_uri,
'ssl_version': PROTOCOL_TLSv1_2,
'cert_reqs': CERT_REQUIRED # Certificates are required and validated
}
auth_provider = PlainTextAuthProvider(username=db_account, password=db_key)
endpoint_uri = db_account + '.cassandra.cosmosdb.azure.com'
cluster = Cluster([endpoint_uri],
port=10350,
auth_provider=auth_provider,
ssl_options=ssl_opts)
session = cluster.connect(keyspace)
#Workspace.get("azmlsiws", subscription_id="0b753943-9062-4ec0-9739-db8fb455aeba", resource_group="CommercialCyberAzure")
if isNewLabeledData(eh_url, eh_offset_url, eh_account, eh_key):
model = retrainClassifier(session, db_config)
repredictImages(session, model, db_config)
def PrintTable(rows):
t = PrettyTable(['UserID', 'Name', 'City'])
for r in rows:
t.add_row([r.user_id, r.user_name, r.user_bcity])
print t
ssl_opts = {
'ca_certs': DEFAULT_CA_BUNDLE_PATH,
'ssl_version': PROTOCOL_TLSv1_2,
}
if 'certpath' in cfg.config:
ssl_opts['ca_certs'] = cfg.config['certpath']
auth_provider = PlainTextAuthProvider(username=cfg.config['username'],
password=cfg.config['password'])
cluster = Cluster([cfg.config['contactPoint']],
port=cfg.config['port'],
auth_provider=auth_provider,
ssl_options=ssl_opts)
session = cluster.connect()
print "\nCreating Keyspace"
session.execute(
'CREATE KEYSPACE IF NOT EXISTS uprofile WITH replication = {\'class\': \'NetworkTopologyStrategy\', \'datacenter\' : \'1\' }'
)
print "\nCreating Table"
session.execute(
'CREATE TABLE IF NOT EXISTS uprofile.user (user_id int PRIMARY KEY, user_name text, user_bcity text)'
)
## Simple example for studying big data platforms
from cassandra import ConsistencyLevel
from cassandra.cluster import Cluster
from cassandra.query import SimpleStatement
from cassandra.auth import PlainTextAuthProvider
from time import time
import argparse
parser = argparse.ArgumentParser()
parser.add_argument('--hosts', help='cassandra host "host1,host2,host3"')
parser.add_argument('--u',help='user name')
parser.add_argument('--p',help='password')
parser.add_argument('--q',help='input query')
args = parser.parse_args()
hosts=args.hosts.split(',')
auth_provider = PlainTextAuthProvider(
username=args.u, password=args.p)
if __name__ == "__main__":
#you can add some fake ips into cluster: '10.166.0.14','192.168.1.1'
cluster = Cluster(hosts,port=9042,auth_provider=auth_provider)
session = cluster.connect()
## Change the consistency level to see
input= args.q
#copy a line of the bird song in csv file here
start=time()
## enable only 1 option
consistency_level =ConsistencyLevel.ONE
#consistency_level =ConsistencyLevel.QUORUM
#consistency_level =ConsistencyLevel.ALL
query = SimpleStatement(input,
consistency_level=consistency_level)
from cassandra.cluster import Cluster
from cassandra.auth import PlainTextAuthProvider
cloud_config= {
'secure_connect_bundle': '<<secure-connect-Exercisewithfriends.zip'
}
auth_provider = PlainTextAuthProvider('<username>', '<password>')
cluster = Cluster(cloud=cloud_config, auth_provider=auth_provider)
session = cluster.connect()
row = session.execute("select release_version from system.local").one()
if row:
print(row[0])
else:
print("An error occurred.")
writer.writerow(data)
def load_geo_data(session, geopath):
geo_prepared = session.prepare(GEO_INSERT_STATEMENT)
with open(geopath, 'rb') as f:
reader = csv.reader(f)
for row in reader:
geo_bound = geo_prepared.bind(map(uuid.UUID, row[0:2]) + row[2:])
session.execute(geo_bound)
if __name__ == '__main__':
meta_path = 'metadata.json'
geo_path = 'geodata.csv'
auth_provider = PlainTextAuthProvider(username='******',
password='******')
cluster = Cluster(['dc0vm0', 'dc0vm1', 'dc0vm2'],
auth_provider=auth_provider)
session = cluster.connect()
session.execute(META_CF_DROP_STATEMENT)
session.execute(RANK_CF_DROP_STATEMENT)
session.execute(GEO_CF_DROP_STATEMENT)
session.execute(KS_CREATION_STATEMENT)
session.execute(GEO_CF_CREATION_STATEMENT)
session.execute(META_CF_CREATION_STATEMENT)
session.execute(RANK_CF_CREATION_STATEMENT)
meta_prepared = session.prepare(META_INSERT_STATEMENT)
rank_prepared = session.prepare(RANK_INSERT_STATEMENT)
print('Loading into Keyspace', sys.argv[1])
print('loading geo')
def createsession(self):
ap = PlainTextAuthProvider(username='******', password='******')
self.cluster = Cluster(['127.0.0.1'], port=9042, auth_provider=ap)
self.session = self.cluster.connect(self.keyspace)
return self.session
import uuid
import json
from ssl import SSLContext, PROTOCOL_TLSv1, CERT_REQUIRED
from cassandra.auth import PlainTextAuthProvider
from cassandra.cluster import Cluster, ExecutionProfile, EXEC_PROFILE_DEFAULT, ConsistencyLevel
CASSANDRA_CREDS = os.environ['CASSANDRA_CREDS']
AWS_DEFAULT_REGION = os.environ['AWS_DEFAULT_REGION']
secret_client = boto3.client('secretsmanager')
secret_response = secret_client.get_secret_value(SecretId=CASSANDRA_CREDS)
secret = json.loads(secret_response.get('SecretString'))
cassandra_user = secret['ServiceSpecificCredential']['ServiceUserName']
cassandra_password = secret['ServiceSpecificCredential']['ServicePassword']
auth_provider = PlainTextAuthProvider(username=cassandra_user,
password=cassandra_password)
ssl_context = SSLContext(PROTOCOL_TLSv1)
ssl_context.load_verify_locations('AmazonRootCA1.pem')
ssl_context.verify_mode = CERT_REQUIRED
cluster = Cluster(['cassandra.{}.amazonaws.com'.format(AWS_DEFAULT_REGION)],
port=9142,
ssl_context=ssl_context,
auth_provider=auth_provider)
session = cluster.connect()
def handler(event, context):
response = {'statusCode': 405}
if event['httpMethod'] in ['PUT', 'POST']:
do_upsert(json.loads(event['body']))
import dash
import psycopg2
from cassandra.cluster import Cluster
from cassandra.auth import PlainTextAuthProvider
import datetime
app = dash.Dash(__name__)
server = app.server
app.title = 'SmartSquare MotionPathDashboard'
app.config.suppress_callback_exceptions = True
# API keys and datasets
app.mapbox_access_token = 'pk.eyJ1Ijoic25vb3B0aGVub29iIiwiYSI6ImNqdm1qdTd2cDFkdWg0YXJ1YXZwNTFtdmcifQ.7ABtAp_1NyyHFFBbfAeIwQ'
app.connection = psycopg2.connect(database="gisdb", user="******", host="localhost", port="5432", password="******")
app.connection.set_session(autocommit=True)
app.cursor = app.connection.cursor()
auth_provider = PlainTextAuthProvider(
username='******', password='')
app.cluster = Cluster([''], auth_provider=auth_provider)
app.session = app.cluster.connect('master_dataset')
# Boostrap CSS.
app.css.append_css({'external_url': 'https://codepen.io/amyoshino/pen/jzXypZ.css'})
app.last_update_time = datetime.datetime.now()
#!/usr/bin/env python3
from cassandra import ConsistencyLevel
from cassandra.cluster import Cluster
from cassandra.query import BatchType, SimpleStatement
from ssl import SSLContext, PROTOCOL_TLSv1, CERT_REQUIRED
from cassandra.auth import PlainTextAuthProvider
import sys, os
ssl_context = SSLContext(PROTOCOL_TLSv1)
ssl_context.load_verify_locations('./AmazonRootCA1.pem')
ssl_context.verify_mode = CERT_REQUIRED
auth_provider = PlainTextAuthProvider(username=os.getenv('USER'), password=os.getenv('PASS'))
cluster = Cluster(['cassandra.us-west-2.amazonaws.com'], ssl_context=ssl_context, auth_provider=auth_provider, port=9142)
session = cluster.connect('user')
pstmt = session.prepare("INSERT INTO password (shaone,count) VALUES (?,?)")
pstmt.consistency_level = ConsistencyLevel.LOCAL_QUORUM
f = open(sys.argv[1],'r')
line = f.readline()
while line:
(shaone,count) = line.split(':')
session.execute(pstmt, (shaone, int(count)))
line = f.readline()
f.close()
#!/usr/bin/env python3
from cassandra.cluster import Cluster
from cassandra.auth import PlainTextAuthProvider
auth_provider = PlainTextAuthProvider(username='******', password='******')
cluster = Cluster(['YOURADDRESS'], auth_provider=auth_provider)
session = cluster.connect()
session.exicute( CREATE KEYSPACE test WITH REPLICATION = { 'class' : 'NetworkTopologyStrategy', 'dc1' : 3 })
session.exicute( CREATE TABLE test.users (username text, name text, age int, PRIMARY KEY(username));)
session.exicute(INSERT INTO users(username,name,age) VALUES ('EricZ','Eric Zietlow',67);)
cluster.shutdown()
# Make the request to the resources api
# response = requests.get('https://scheduler.monroe-system.eu/v1/resources', cert=('/home/dimitris/monroe/certificate.pem', '/home/dimitris/monroe/privateKeyClear.pem'))
response = requests.get('https://scheduler.monroe-system.eu/v1/resources',
cert=(args.certificate, args.privateKey))
# In case of error print the error and exit
if response.status_code != 200:
# This means something went wrong.
print('GET /v1/resources {}'.format(response.status_code))
print('Response Headers: {}'.format(response.headers))
sys.exit()
# Make the connection to the database
authProvider = PlainTextAuthProvider(username='******',
password='******')
cluster = Cluster(['127.0.0.1'],
9042,
auth_provider=authProvider,
connect_timeout=15)
session = cluster.connect('monroe')
# Start the combining algorithm at the immediately higher multiple of 5
step = args.interval
initialTime = step * (args.startTimeStamp / step + 1)
# Iterate over the nodes (each resource is a node)
for resource in response.json():
# print('{} {}'.format(resource['id'], resource['project']))
# Skip nodes that do not belong to the selected product and nodes that are not in the deployed or testing state
from cassandra.auth import PlainTextAuthProvider
from cassandra.cluster import Cluster
from datetime import timedelta, datetime
import config
""" Find the travel time for station Foster NB for 5-minute intervals for Sept 22, 2011 """
ap = PlainTextAuthProvider(username=config.username, password=config.password)
node_ips = config.hosts
cluster = Cluster(node_ips,
protocol_version=4,
auth_provider=ap,
port=config.port)
session = cluster.connect('part_3_version_0')
results = session.execute(
"""
SELECT detectorid, length
FROM detectors_by_highway
WHERE locationtext = %s
""", ["Foster NB"])
length = results[0].length
temp = ''
for row in results:
temp += str(row.detectorid) + ', '
idList = temp[0:-2]
intervals = []
first = datetime(2011, 9, 22, tzinfo=None)
increment = timedelta(minutes=5)
last = datetime(2011, 9, 23, tzinfo=None)
#!/usr/bin/python
import time
import sys
from cassandra.cluster import Cluster
from cassandra.auth import PlainTextAuthProvider
from cassandra.policies import DCAwareRoundRobinPolicy
from cassandra import ConsistencyLevel
from random import randint
#Configuration
contactpoints = ['172.31.14.14', '172.31.11.194']
auth_provider = PlainTextAuthProvider(username='******', password='******')
keyspace = "poctest"
localDC = "dc1"
delay = 1
CL = ConsistencyLevel.LOCAL_QUORUM
#CL = ConsistencyLevel.LOCAL_ONE
print "Connecting to cluster"
cluster = Cluster(contact_points=contactpoints,
load_balancing_policy=DCAwareRoundRobinPolicy(
local_dc=localDC, used_hosts_per_remote_dc=3),
auth_provider=auth_provider)
session = cluster.connect()
session.default_consistency_level = CL
session.execute(
def import_schema(schema, srv_options, options, restriction_type, restricts):
if ISDEBUG:
logger.log(
u"import schema {0} requiested with options {1}; restriction type: {2}; restrictions: {3}"
.format(schema, options, restriction_type, restricts))
if "hosts" not in srv_options:
logger.log("The hosts parameter is needed, setting to localhost.",
WARNING)
hosts = srv_options.get("hosts", "localhost").split(",")
if "port" not in srv_options:
logger.log("The port parameter is needed, setting to 9042.", WARNING)
port = srv_options.get("port", "9042")
username = srv_options.get("username", None)
password = srv_options.get("password", None)
with_row_id = options.get('with_row_id', 'True') == 'True'
names_mapping = options.get('mapping', '').split(';')
mapping_dict = {}
mapping_dict_backward = {}
for s in names_mapping:
kp = s.split('=')
if len(kp) != 2:
continue
key = kp[0].strip()
value = kp[1].strip()
mapping_dict[key] = value
mapping_dict_backward[value] = key
cluster = Cluster(hosts)
if (username is not None):
cluster.auth_provider = PlainTextAuthProvider(username=username,
password=password)
# Cassandra connection init
session = cluster.connect()
keyspace = cluster.metadata.keyspaces[schema]
cassandra_tables = []
tables = keyspace.tables
views = keyspace.views
if restriction_type is None:
for t in tables:
if t in tables:
cassandra_tables.append(tables[t])
else:
cassandra_tables.append(views[t])
elif restriction_type == 'limit':
for r in restricts:
t_name = r
if t_name in mapping_dict_backward:
t_name = mapping_dict_backward[t_name]
if t_name in tables:
cassandra_tables.append(tables[t_name])
else:
cassandra_tables.append(views[t_name])
elif restriction_type == 'except':
for t in tables:
if t not in restricts:
if t in tables:
cassandra_tables.append(tables[t])
else:
cassandra_tables.append(views[t])
pg_tables = []
for c_table in cassandra_tables:
if ISDEBUG:
logger.log("Importing table {0}...".format(c_table.name))
pg_table_name = c_table.name
if pg_table_name in mapping_dict:
if ISDEBUG:
logger.log(
"Cassandra table name '{0}' maps to PostgreSQL table name '{1}'"
.format(pg_table_name, mapping_dict[pg_table_name]))
pg_table_name = mapping_dict[pg_table_name]
pg_table = TableDefinition(pg_table_name)
pg_table.options['keyspace'] = schema
pg_table.options['columnfamily'] = c_table.name
for c_column_name in c_table.columns:
cql_type = c_table.columns[c_column_name].cql_type
pg_type = types_mapper.get_pg_type(cql_type)
if ISDEBUG:
logger.log(
"Adding column {0} with PostgreSQL type {2} (CQL type {1})"
.format(c_column_name, cql_type, pg_type))
pg_table.columns.append(
ColumnDefinition(c_column_name, type_name=pg_type))
if with_row_id:
pg_table.columns.append(
ColumnDefinition('__rowid__', type_name='text'))
pg_tables.append(pg_table)
if ISDEBUG:
logger.log("Table imported: {0}".format(c_table.name))
session.shutdown()
return pg_tables
# Create flag strings
flags = []
for env in ENVIRONMENTS:
for req_cat in REQUEST_CATEGORIES:
for id in SERVICE_TYPE_IDS:
flags.append([env, req_cat, id, 0, random.randint(0, 2)])
flags.append([env, req_cat, id, 1, random.randint(0, 2)])
# Cassandra Connection
cass_address = 'dev-cassandra.ksg.int'
cass_port = 9042
cass_username = '******'
cass_password = '******'
cass_namespace = 'CassandraPractice'
authentication = PlainTextAuthProvider(username=cass_username,
password=cass_password)
cluster = Cluster([cass_address], port=cass_port, auth_provider=authentication)
session = cluster.connect(cass_namespace)
cass_query_prefix = "INSERT INTO \"Flags\" (\"Environment\", \"Request Category\", \"Service Type ID\", \"WFE\", \"Value\") VALUES ("
print(cass_query_prefix)
# Insert flags to cassandra table
for flag in flags:
session.execute(cass_query_prefix + "'" + flag[0] + "'," + "'" + flag[1] +
"'," + str(flag[2]) + ',' + str(flag[3]) + ',' +
str(flag[4]) + ');')
session.shutdown()
flipt_url = 'http://flipt-demo.devops-sandbox.k1d.k8.cin.kore.korewireless.com/api/v1/flags'
from pprint import pprint
import json
import os
import datetime
import operator
import sys
import getopt
import cassandra
from cassandra.cluster import Cluster
from cassandra.auth import PlainTextAuthProvider
cloud_config = {
'secure_connect_bundle': 'secure-connect-teaminferno.zip'
}
auth_provider = PlainTextAuthProvider('PKulaMpxCDcyZsxHoLeorxdE',
'R1KL7l1u,awPgafa0-G3Xjt5QAjk,gTtz.qgmQSrsstUIdQnOoq_jgI,77nPA9upDOOYj2+ZefBMXudz+dFfF7IYPMGo56gz7xD337Nrcaufv3KKh,kzaS,0_xuCflNI')
cluster = Cluster(cloud=cloud_config, auth_provider=auth_provider)
cqlsession = cluster.connect('yvideos')
def reverse(lst):
return[ele for ele in reversed(lst)]
def valueswap(str):
if str == "00":
return "12:00am"
if str == "01":
return "1:00am"
if str == "02":
return "2:00am"
def get_auth_provider(user, password):
return PlainTextAuthProvider(username=user, password=password)
from cassandra.auth import PlainTextAuthProvider
from cassandra.cluster import Cluster
contact_points = ['92.53.78.60']
keyspace_name = 'ems'
port = 9042
username = '******'
password = '******'
auth = PlainTextAuthProvider(username=username, password=password)
cluster = Cluster(contact_points, auth_provider=auth, port=port)
session = cluster.connect(keyspace_name)
rows = session.execute(
"DELETE FROM ems.data WHERE device_id=00000000-0000-0000-0000-000000000000 and data_source_id=2 IF EXISTS"
)
for row in rows:
print(row)
-------------------------- 連接遠程數據庫 # -*- encoding: utf-8 -*- from cassandra import ConsistencyLevel # 引入Cluster模塊 from cassandra.cluster import Cluster # 引入DCAwareRoundRobinPolicy模塊，可用來自定義驅動程序的行為 # from cassandra.policies import DCAwareRoundRobinPolicy from cassandra.auth import PlainTextAuthProvider from cassandra.query import SimpleStatement import pandas as pd # 配置Cassandra集群的IP contact_points = [‘1.1.1.1‘, ‘2.2.2.2‘, ‘3.3.3.3‘] # 配置登陸Cassandra集群的賬號和密碼 auth_provider = PlainTextAuthProvider(username=‘XXX‘, password=‘XXX‘) # 創建一個Cassandra的cluster cluster = Cluster(contact_points=contact_points, auth_provider=auth_provider) # 連接並創建一個會話 session = cluster.connect() # 定義一條cql查詢語句 cql_str = ‘select * from keyspace.table limit 5;‘ simple_statement = SimpleStatement(cql_str,consistency_level=ConsistencyLevel.ONE) # 對語句的執行設置超時時間為None execute_result = session.execute(simple_statement, timeout=None) # 獲取執行結果中的原始數據 result = execute_result._current_rows # 把結果轉成DataFrame格式 result = pd.DataFrame(result) # 把查詢結果寫入csv result.to_csv(‘連接遠程數據庫.csv‘, mode=‘a‘, header=True)
def processEntityImages(choice_blobs, db_account_name, db_account_key,
ca_file_uri, db_config, cs_account, cs_key, eh_url,
eh_account, eh_key):
'''
Tests entity images for the presence of a face using Azure Cognitive Services, extracts the face
based on the provided bounding box, applies the facial classifier if available and then
writes the raw image, face to CosmosDB
'''
#Initialize the cognitive services account to perform facial detection
BASE_CS_URL = 'https://virginia.api.cognitive.microsoft.us/face/v1.0/' # Replace with your regional Base URL
CF.Key.set(cs_key)
CF.BaseUrl.set(BASE_CS_URL)
def extractFace(image_bytes):
'''
'''
face_list = CF.face.detect(image_bytes)
if len(face_list) == 1:
face_rectangle = face_list[0]['faceRectangle']
nparr = np.fromstring(image_bytes, np.uint8)
img_byte = Image.open(io.BytesIO(image_bytes))
face_iso_image = img_byte.crop(
(face_rectangle['left'], face_rectangle['top'],
face_rectangle['left'] + face_rectangle['width'],
face_rectangle['top'] + face_rectangle['height']))
return face_iso_image
else:
return None
#Convert the base image to PIL object, then detect and extract the face component of the image
blob_image_faces = list(
map(
lambda blob_tuple:
(blob_tuple[0], Image.open(io.BytesIO(blob_tuple[1])),
extractFace(blob_tuple[1])), choice_blobs))
logging.debug("Face detection run on {0} images".format(
len(blob_image_faces)))
#Connect to the database
ssl_opts = {
'ca_certs': ca_file_uri,
'ssl_version': PROTOCOL_TLSv1_2,
'cert_reqs': CERT_REQUIRED # Certificates are required and validated
}
auth_provider = PlainTextAuthProvider(username=db_account_name,
password=db_account_key)
endpoint_uri = db_account_name + '.cassandra.cosmosdb.azure.com'
cluster = Cluster([endpoint_uri],
port=10350,
auth_provider=auth_provider,
ssl_options=ssl_opts)
#If no db config file is passed, look for the container environment variables
if db_config is None:
keyspace = os.environ['DB_KEYSPACE']
personaTableName = os.environ['DB_PERSONA_TABLE']
subPersonaTableName = os.environ['DB_SUB_PERSONA_TABLE']
subPersonaFaceEdgeTableName = os.environ['DB_SUB_PERSONA_EDGE_TABLE']
faceSubPersonaEdgeTableName = os.environ['DB_SUB_PERSONA_EDGE_TABLE']
rawImageTableName = os.environ['DB_RAW_IMAGE_TABLE']
faceTableName = os.environ['DB_REFINED_IMAGE_TABLE']
#Otherwise load db config
else:
keyspace = db_config['db-keyspace']
personaTableName = db_config['db-persona-table']
subPersonaTableName = db_config['db-sub-persona-table']
subPersonaFaceEdgeTableName = db_config[
'db-sub-persona-face-edge-table']
faceSubPersonaEdgeTableName = db_config[
'db-face-sub-persona-edge-table']
rawImageTableName = db_config['db-raw-image-table']
faceTableName = db_config['db-face-image-table']
#Prepare Cosmos DB session and insertion queries
session = cluster.connect(keyspace)
personaInsertQuery = session.prepare("INSERT INTO " + personaTableName +
" (persona_name) VALUES (?)")
subPersonaInsertQuery = session.prepare(
"INSERT INTO " + subPersonaTableName +
" (sub_persona_name, persona_name) VALUES (?, ?)")
subPersonaFaceEdgeInsertQuery = session.prepare(
"INSERT INTO " + subPersonaFaceEdgeTableName +
" (sub_persona_name, assoc_face_id, label_v_predict_assoc_flag) VALUES (?,?,?)"
)
faceSubPersonaEdgeInsertQuery = session.prepare(
"INSERT INTO " + faceSubPersonaEdgeTableName +
" (sub_persona_name, assoc_face_id, label_v_predict_assoc_flag) VALUES (?,?,?)"
)
rawInsertQuery = session.prepare(
"INSERT INTO " + rawImageTableName +
" (image_id, file_uri, image_bytes) VALUES (?,?,?)")
refinedInsertQuery = session.prepare(
"INSERT INTO " + faceTableName +
" (face_id, raw_image_edge_id, face_bytes, feature_bytes) VALUES (?,?,?,?)"
)
client = EventHubClient(eh_url,
debug=False,
username=eh_account,
password=eh_key)
sender = client.add_sender(partition="0")
client.run()
face_write_count = 0
face_label_write_count = 0
face_unlabeled_write_count = 0
for (blob, image_bytes, face_bytes) in blob_image_faces:
if face_bytes is not None:
file_name = blob.name
(entity, usage, number) = file_name.split('-')
#Generate the face classifier features from the face using VGG-face on Keras
if face_bytes.mode != "RGB":
face_bytes = face_bytes.convert("RGB")
face_bytes = face_bytes.resize((img_width, img_height))
image = img_to_array(face_bytes)
image = np.expand_dims(image, axis=0)
image = imagenet_utils.preprocess_input(image)
stuff = vgg_face_feature_gen.predict(image, batch_size=1).flatten()
writer = avro.io.DatumWriter(schema)
bytes_writer = io.BytesIO()
encoder = avro.io.BinaryEncoder(bytes_writer)
writer.write({"features": stuff.tolist()}, encoder)
face_feature_bytes = bytes_writer.getvalue()
#For each image extract the label and use to generate a persona, redundant writes will cancel out
#Writes the entity label to the persona table
#For the time being also write the entity label to the subpersona table and associate with the persona table
session.execute(personaInsertQuery, (entity, ))
session.execute(subPersonaInsertQuery, (entity, entity))
logging.info(
"Writing persona, sub-persona {0} to DB table {1}".format(
entity, subPersonaTableName))
#Resizes the image to ensure the write query does not exceed maximum size
width, height = image_bytes.size
if width > height:
transform_factor = 256 / width
else:
transform_factor = 256 / height
compact_image_bytes = image_bytes.resize(
(round(height * transform_factor),
round(width * transform_factor)))
#Writes the raw image to its table
imgByteArr = io.BytesIO()
compact_image_bytes.save(imgByteArr, format='PNG')
compact_image_bytes = imgByteArr.getvalue()
hashed_bytes = hashlib.md5(compact_image_bytes).digest()
hashed_bytes_int = int.from_bytes(
hashed_bytes, byteorder='big') #Good identifier, sadly un
image_hash = str(
hashed_bytes_int
) #Stupid workaround to Python high precision int incompatability
session.execute(rawInsertQuery,
(image_hash, file_name, compact_image_bytes))
logging.info("Writing raw image to DB {0}".format(image_hash))
#Resizes the image to ensure the write query does not exceed maximum size
width, height = face_bytes.size
if width > height:
transform_factor = 256 / width
else:
transform_factor = 256 / height
compact_face_bytes = face_bytes.resize(
(round(height * transform_factor),
round(width * transform_factor)))
#Write each face extracted from the image to the DB as a refined image
imgByteArr = io.BytesIO()
compact_face_bytes.save(imgByteArr, format='PNG')
compact_face_bytes = imgByteArr.getvalue()
face_hash_bytes = hashlib.md5(compact_face_bytes).digest()
face_hashed_bytes_int = int.from_bytes(
face_hash_bytes, byteorder='big') #Good identifier, sadly un
face_hash = str(
face_hashed_bytes_int
) #Stupid workaround to Python high precision int incompatability
session.execute(refinedInsertQuery,
(face_hash, image_hash, compact_face_bytes,
face_feature_bytes))
logging.info("Writing face image to DB {0}".format(face_hash))
face_write_count += 1
#If the data is part of the training set, write edges between the sub-personas, face images
if usage == "Train":
session.execute(subPersonaFaceEdgeInsertQuery,
(entity, face_hash, True))
session.execute(faceSubPersonaEdgeInsertQuery,
(entity, face_hash, True))
sender.send(
EventData(
json.dumps({
"EVENT_TYPE":
"LABEL_WRITE",
"LABEL_INDEX":
face_hash,
"WRITE_TIMESTAMP":
datetime.datetime.now().timestamp()
})))
logging.info("Writing face label to DB {0}".format(face_hash))
face_label_write_count += 1
else:
#Silly engineering workaround to make it easier to find the unlabeled images later for re-prediction
session.execute(subPersonaFaceEdgeInsertQuery,
("TEMPORARY", face_hash, False))
session.execute(faceSubPersonaEdgeInsertQuery,
("TEMPORARY", face_hash, False))
logging.info(
"Writing unlabeled face {0} to DB".format(face_hash))
face_unlabeled_write_count += 1
#Otherwise do not write an edge, these will be predicted later by the training service
#session.close()
client.stop()
logging.info("Wrote {0} faces to DB".format(face_write_count))
logging.info("Wrote {0} face labels to DB".format(face_label_write_count))
from os import environ
from cassandra.cqlengine import connection
from cassandra.auth import PlainTextAuthProvider
from stream_framework import settings
auth_provider = PlainTextAuthProvider(username=environ['CASSANDRA_USERNAME'],
password=environ['CASSANDRA_PASSWORD'])
def setup_connection():
connection.setup(hosts=[environ['CASSANDRA_HOST']],
consistency=settings.STREAM_CASSANDRA_CONSISTENCY_LEVEL,
default_keyspace=settings.STREAM_DEFAULT_KEYSPACE,
auth_provider=auth_provider,
**settings.CASSANDRA_DRIVER_KWARGS)
from cassandra.cluster import Cluster
from cassandra.auth import PlainTextAuthProvider
cloud_config = {'secure_connect_bundle': 'secure-connect-pokemon.zip'}
auth_provider = PlainTextAuthProvider('<<CLIENT ID>>', '<<CLIENT SECRET>>')
cluster = Cluster(cloud=cloud_config, auth_provider=auth_provider)
session = cluster.connect()
row = session.execute("select release_version from system.local").one()
if row:
print(row[0])
else:
print("An error occurred.")
def main():
arg_spec = {
'keyspace': {
'type': 'str',
'required': True,
},
'replication_strategy': {
'type': 'dict',
'required': True,
},
'durable_writes': {
'type': 'bool',
'required': True,
},
'login_user': {
'type': 'str',
'required': True,
},
'login_password': {
'type': 'str',
'required': True,
'no_log': True
},
'login_hosts': {
'type': 'list',
'required': True,
},
'login_port': {
'type': 'int',
'default': 9042,
'required': False,
},
'protocol': {
'type': 'int',
'default': 3,
'required': False,
},
}
module = AnsibleModule(argument_spec=arg_spec)
keyspace = module.params['keyspace']
replication_strategy = module.params['replication_strategy']
durable_writes = module.params['durable_writes']
login_hosts = module.params['login_hosts']
login_port = module.params['login_port']
login_user = module.params['login_user']
login_password = module.params['login_password']
protocol = module.params['protocol']
if not cassandra_dep_found:
module.fail_json(msg="the python cassandra-driver module is required")
if not json_dep_found:
module.fail_json(
msg="the python json or simplejson module is required")
try:
if not login_user:
cluster = Cluster(login_hosts, port=login_port)
else:
auth_provider = PlainTextAuthProvider(username=login_user,
password=login_password)
cluster = Cluster(login_hosts,
auth_provider=auth_provider,
protocol_version=protocol,
port=login_port)
session = cluster.connect()
session.row_factory = dict_factory
except Exception, e:
module.fail_json(
msg="unable to connect to cassandra, check login_user and " +
"login_password are correct. Exception message: %s" % e)
def _connect(
contact_points=None, port=None, cql_user=None, cql_pass=None, protocol_version=None
):
"""
Connect to a Cassandra cluster.
:param contact_points: The Cassandra cluster addresses, can either be a string or a list of IPs.
:type contact_points: str or list of str
:param cql_user: The Cassandra user if authentication is turned on.
:type cql_user: str
:param cql_pass: The Cassandra user password if authentication is turned on.
:type cql_pass: str
:param port: The Cassandra cluster port, defaults to None.
:type port: int
:param protocol_version: Cassandra protocol version to use.
:type port: int
:return: The session and cluster objects.
:rtype: cluster object, session object
"""
# Lazy load the Cassandra cluster and session for this module by creating a
# cluster and session when cql_query is called the first time. Get the
# Cassandra cluster and session from this module's __context__ after it is
# loaded the first time cql_query is called.
#
# TODO: Call cluster.shutdown() when the module is unloaded on
# master/minion shutdown. Currently, Master.shutdown() and Minion.shutdown()
# do nothing to allow loaded modules to gracefully handle resources stored
# in __context__ (i.e. connection pools). This means that the connection
# pool is orphaned and Salt relies on Cassandra to reclaim connections.
# Perhaps if Master/Minion daemons could be enhanced to call an "__unload__"
# function, or something similar for each loaded module, connection pools
# and the like can be gracefully reclaimed/shutdown.
if (
__context__
and "cassandra_cql_returner_cluster" in __context__
and "cassandra_cql_returner_session" in __context__
):
return (
__context__["cassandra_cql_returner_cluster"],
__context__["cassandra_cql_returner_session"],
)
else:
contact_points = _load_properties(
property_name=contact_points, config_option="cluster"
)
contact_points = (
contact_points
if isinstance(contact_points, list)
else contact_points.split(",")
)
port = _load_properties(
property_name=port, config_option="port", set_default=True, default=9042
)
cql_user = _load_properties(
property_name=cql_user,
config_option="username",
set_default=True,
default="cassandra",
)
cql_pass = _load_properties(
property_name=cql_pass,
config_option="password",
set_default=True,
default="cassandra",
)
protocol_version = _load_properties(
property_name=protocol_version,
config_option="protocol_version",
set_default=True,
default=4,
)
try:
auth_provider = PlainTextAuthProvider(username=cql_user, password=cql_pass)
ssl_opts = _get_ssl_opts()
if ssl_opts:
cluster = Cluster(
contact_points,
port=port,
auth_provider=auth_provider,
ssl_options=ssl_opts,
protocol_version=protocol_version,
compression=True,
)
else:
cluster = Cluster(
contact_points,
port=port,
auth_provider=auth_provider,
protocol_version=protocol_version,
compression=True,
)
for recontimes in range(1, 4):
try:
session = cluster.connect()
break
except OperationTimedOut:
log.warning(
"Cassandra cluster.connect timed out, try %s", recontimes
)
if recontimes >= 3:
raise
# TODO: Call cluster.shutdown() when the module is unloaded on shutdown.
__context__["cassandra_cql_returner_cluster"] = cluster
__context__["cassandra_cql_returner_session"] = session
__context__["cassandra_cql_prepared"] = {}
log.debug(
"Successfully connected to Cassandra cluster at %s", contact_points
)
return cluster, session
except TypeError:
pass
except (ConnectionException, ConnectionShutdown, NoHostAvailable):
log.error("Could not connect to Cassandra cluster at %s", contact_points)
raise CommandExecutionError(
"ERROR: Could not connect to Cassandra cluster."
)
def init_cluster(self):
ap = PlainTextAuthProvider(username=os.environ['DB_USER'],
password=os.environ['DB_PASS'])
self.cluster = Cluster([os.environ['DB_ADDR']],
auth_provider=ap,
idle_heartbeat_interval=DB_IDLE)