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(
Ejemplo n.º 2
0
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)" \
Ejemplo n.º 3
0
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)
Ejemplo n.º 4
0
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
Ejemplo n.º 5
0
        #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)
Ejemplo n.º 6
0
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)
Ejemplo n.º 7
0
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)
Ejemplo n.º 9
0
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.")
Ejemplo n.º 10
0
            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')
Ejemplo n.º 11
0
 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']))
Ejemplo n.º 13
0
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()

Ejemplo n.º 14
0
#!/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()
Ejemplo n.º 15
0
#!/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()
Ejemplo n.º 16
0
    # 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
Ejemplo n.º 17
0
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)
Ejemplo n.º 18
0
#!/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(
Ejemplo n.º 19
0
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
Ejemplo n.º 20
0
# 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'
Ejemplo n.º 21
0
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"
Ejemplo n.º 22
0
def get_auth_provider(user, password):
    return PlainTextAuthProvider(username=user, password=password)
Ejemplo n.º 23
0
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)
Ejemplo n.º 24
0
--------------------------
連接遠程數據庫
# -*- 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)
Ejemplo n.º 25
0
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))
Ejemplo n.º 26
0
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)
Ejemplo n.º 27
0
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)
Ejemplo n.º 29
0
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."
            )
Ejemplo n.º 30
0
 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)