def Insert(Bear):
date = datetime.strptime(Bear.timestamp, "%Y-%m-%d %H:%M:%S.%f")
db = Database('Honeypot',
db_url=CLICKHOUSEIP + ':' + CLICKHOUSEPORT,
username=CLICKHOUSEUSER,
password=CLICKHOUSEPASSWORD)
DBBear = BearRequests_development(
EventDate=date.date(),
RequestTime=date,
RequestPath=Bear.path,
RequestCommand=Bear.command,
RequestVersion=Bear.version,
RequestRaw=Bear.rawrequest,
ProbeName=Bear.hostname,
RequestDetectionID=Bear.isDetected,
BotIP=Bear.ip,
BotCountry=Bear.country,
BotUA=Bear.ua,
BotContinent=Bear.continent,
BotTracert=Bear.tracert,
BotDNSName=Bear.dnsname,
)
db.insert({
DBBear,
})
async def setup():
session = aiohttp.ClientSession()
loop = asyncio.get_event_loop()
database = Database(CONFIG.db_name,
db_url=f"http://{CONFIG.host_name}:8123/")
logger = Logger(database)
dispatcher = DiffDepthStreamDispatcher(database, logger)
logger.log_msg("Starting event loop...", LoggingLevel.INFO)
for symbol in CONFIG.symbols:
if "USD_" in symbol:
loop.create_task(
handle_depth_stream(
symbol[4:],
session,
dispatcher,
database,
logger,
loop,
AssetType.USD_M,
))
elif "COIN_" in symbol:
loop.create_task(
handle_depth_stream(
symbol[5:],
session,
dispatcher,
database,
logger,
loop,
AssetType.COIN_M,
))
else:
loop.create_task(
handle_depth_stream(symbol, session, dispatcher, database,
logger, loop, AssetType.SPOT))
def setUp(self):
self.database = Database('test-db', log_statements=True)
try:
self.database.create_table(DecimalModel)
except ServerError as e:
# This ClickHouse version does not support decimals yet
raise unittest.SkipTest(e.message)
def setUp(self):
self.database = Database('test-db')
self.database.create_table(TestTable)
self.database.create_table(CustomPartitionedTable)
self.database.insert([TestTable(date_field=date.today())])
self.database.insert(
[CustomPartitionedTable(date_field=date.today(), group_field=13)])
def parse_clickhouse_json(jsonBody, db_name, db_host):
visits_buffer = []
for i in jsonBody:
#print(i)
# inserting data into clickhouse model representation
insert_visits = Actions(
user_id=i['user_id'],
user_name = i['user_name'],
time = i['time'],
event_type = i['event_type'],
screen_name = i['screen_name'],
app_name = i['app_name'],
app_productname = i['app_productname'],
app_version = i['app_version'],
app_publisher =i['app_publisher'],
app_file =i['app_file'],
app_copyright =i['app_copyright'],
app_language = i['app_language'],
file_versioninfo =i['file_versioninfo'],
file_description = i['file_description'],
file_internalname =i['file_internalname'],
file_originalname =i['file_originalname'],
)
visits_buffer.append(insert_visits)
db = Database(db_name, db_url=db_host)
# create table to insert prepared data
db.create_table(Actions)
# insert prepared data into database
db.insert(visits_buffer)
def __init__(self, config_path):
self.config_path = config_path
with open(os.path.expanduser(self.config_path), 'r') as fp:
self.config = json.load(fp)
self.db_name = self.config['db_name']
self.db_url = self.config['db_url']
self.username = self.config['username']
self.password = self.config['password']
self.uri = 'clickhouse://' + self.username + ':' + self.password + '@' + self.db_url.split(
'//')[1] + '/' + self.db_name
self.db = Database(self.db_name,
db_url=self.db_url,
username=self.username,
password=self.password)
self.technical_attr = {
'__class__', '__delattr__', '__dict__', '__dir__', '__doc__',
'__eq__', '__format__', '__ge__', '__getattribute__', '__gt__',
'__hash__', '__init__', '__init_subclass__', '__le__', '__lt__',
'__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__',
'__repr__', '__setattr__', '__sizeof__', '__str__',
'__subclasshook__', '__weakref__', '_database', '_fields',
'_writable_fields', 'create_table_sql', 'drop_table_sql', 'engine',
'from_tsv', 'get_database', 'get_field', 'objects_in', 'readonly',
'set_database', 'system', 'table_name', 'to_dict', 'to_tsv'
}
self.dt_pattern = r'\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}'
self.col_pattern = r'select(.*?)from'
self.table_pattern = r'from (.*?) '
def import_schema(cls, schema, srv_options, options, restriction_type,
restricts):
db_name = options.get('db_name', 'default')
db_url = options.get('db_url', 'http://localhost:8123/')
db = Database(db_name, db_url)
tables = cls._tables_to_import(db, restriction_type, restricts)
return [cls._import_table(db, table, options) for table in tables]
def setUp(self):
super(FieldsTestCase, self).setUp()
self.login()
self.db = Database('test', settings.CLICKHOUSE_URL)
self.db.create_table(ClickhouseAllFields)
object1 = ClickhouseAllFields(**self.values_to_insert)
self.db.insert([object1])
def connect(self):
db_url = "http://{0}:{1}".format(self.args.host or "localhost",
self.args.port or 8123)
self.db = Database(db_name=self.args.database,
db_url=db_url,
username=self.args.username,
password=self.args.password)
return True
def test_create_table_sql(self):
default_db = Database('default')
sql1 = ParentModel.create_table_sql(default_db)
sql2 = Model1.create_table_sql(default_db)
sql3 = Model2.create_table_sql(default_db)
self.assertNotEqual(sql1, sql2)
self.assertNotEqual(sql1, sql3)
self.assertNotEqual(sql2, sql3)
def db(app, request):
db_ = Database(app.name, log_statements=True)
try:
db_.migrate("affo_event_service.migrations")
yield db_
finally:
db_.drop_database()
def __init__(self, options, columns):
super(ClickHouseDataWrapper, self).__init__(options, columns)
# TODO add username, password, debug
self.db_name = options.get('db_name', 'default')
self.db_url = options.get('db_url', 'http://localhost:8123/')
self.db = Database(self.db_name, self.db_url)
self.table_name = options['table_name']
self.model = self._build_model()
self.column_stats = self._get_column_stats(columns)
def test_readonly(self):
orig_database = self.database
self.database = Database(orig_database.db_name, readonly=True)
with self.assertRaises(DatabaseException):
self._insert_and_check(self._sample_data(), len(data))
self.assertEquals(self.database.count(Person), 0)
with self.assertRaises(DatabaseException):
self.database.drop_table(Person)
with self.assertRaises(DatabaseException):
self.database.drop_database()
self.database = orig_database
def test_invalid_slicing(self):
db = Database('system')
qs = Numbers.objects_in(db)
with self.assertRaises(AssertionError):
qs[3:10:2]
with self.assertRaises(AssertionError):
qs[-5]
with self.assertRaises(AssertionError):
qs[:-5]
with self.assertRaises(AssertionError):
qs[50:1]
def setUp(self):
self.database = Database('test-db', log_statements=True)
self.database.add_setting('allow_experimental_decimal_type', 1)
try:
self.database.create_table(DecimalModel)
except ServerError as e:
if 'Unknown setting' in e.message:
# This ClickHouse version does not support decimals yet
raise unittest.SkipTest(e.message)
else:
raise
def test_slicing(self):
db = Database('system')
numbers = list(range(100))
qs = Numbers.objects_in(db)
self.assertEqual(qs[0].number, numbers[0])
self.assertEqual(qs[5].number, numbers[5])
self.assertEqual([row.number for row in qs[:1]], numbers[:1])
self.assertEqual([row.number for row in qs[:10]], numbers[:10])
self.assertEqual([row.number for row in qs[3:10]], numbers[3:10])
self.assertEqual([row.number for row in qs[9:10]], numbers[9:10])
self.assertEqual([row.number for row in qs[10:10]], numbers[10:10])
def __insert__(self, models=[]):
try:
db_url = "http://{0}:{1}".format(self.args.host or "localhost",
self.args.port or 8123)
db = Database(db_name=self.args.database,
db_url=db_url,
username=self.args.username,
password=self.args.password)
ctime = datetime.datetime.now()
db.insert(models)
took = datetime.datetime.now() - ctime
print("{0} Records have been written into clickhouse , Took : {1}".
format(len(models), str(took)))
except Exception as e:
print(e)
print(traceback.format_exc())
raise e
def load():
'''
Loads data from local JSON files into database
TODO: Load data from s3 buckets
'''
db = Database('contrail')
db.create_table(InstanceData)
fil = open('sample_data.json')
fil2 = open('spot_data.json')
d = json.load(fil)
d2 = json.load(fil2)
product_dict, on_demand_dict, reserved_dict, product_keys, on_demand_keys, reserved_keys = getAllAttributes(
d)
spot_data = getSpotData(d2, product_keys + on_demand_keys + reserved_keys)
product_attribute_data = getData(product_dict, product_keys)
on_demand_data = getData(on_demand_dict, on_demand_keys)
reserved_data = getData(reserved_dict, reserved_keys)
combineddata = {
key: on_demand_data[key] + value
for key, value in product_attribute_data.items()
}
data = {}
lst = []
for key, value in combineddata.items():
for k, v in reserved_data.items():
if k.startswith(key):
lst.append(key)
try:
data[k].append(value + v)
except (KeyError):
data[k] = value + v
if key not in set(lst):
try:
data[key].append(value)
except (KeyError):
data[key] = value
items = list(data.values()) + spot_data
for item in items:
instance = InstanceData()
for i in item:
setattr(instance, i[0], i[1])
db.insert([instance])
def init_app(self, app, config_prefix="CLICKHOUSE"):
"""Initialize the `app` for use with this :class:`~ClickHouse`. This is
called automatically if `app` is passed to :meth:`~ClickHouse.__init__`.
The app is configured according to the configuration variables
``PREFIX_DB_NAME``, ``PREFIX_DB_URL``, ``PREFIX_USERNAME``,
``PREFIX_PASSWORD``, ``PREFIX_READONLY``, ``PREFIX_AUTOCREATE``,
``PREFIX_TIMEOUT``, ``PREFIX_VERIFY_SSL_CERT`` and
``LOG_STATEMENTS``,
where "PREFIX" defaults to "CLICKHOUSE".
:param flask.Flask app: the application to configure for use with
this :class:`~ClickHouse`
:param str config_prefix: determines the set of configuration
variables used to configure this :class:`~ClickHouse`
"""
self.config_prefix = config_prefix
if "clickhouse" not in app.extensions:
app.extensions["clickhouse"] = {}
if config_prefix in app.extensions["clickhouse"]:
raise Exception('duplicate config_prefix "%s"' % config_prefix)
def key(suffix):
return "%s_%s" % (config_prefix, suffix)
app.config.setdefault(key("DB_URL"), "http://localhost:8123")
app.config.setdefault(key("DB_NAME"), app.name)
kwargs = {}
for param in self.param_names:
value = app.config.get(key(param))
if value is not None:
kwargs[param.lower()] = value
self.db = Database(**kwargs,)
app.extensions["clickhouse"][config_prefix] = self
def _test_readonly_db(self, username):
self._insert_and_check(self._sample_data(), len(data))
orig_database = self.database
try:
self.database = Database(orig_database.db_name,
username=username,
readonly=True)
with self.assertRaises(DatabaseException):
self._insert_and_check(self._sample_data(), len(data))
self.assertEquals(self.database.count(Person), 100)
list(self.database.select('SELECT * from $table', Person))
with self.assertRaises(DatabaseException):
self.database.drop_table(Person)
with self.assertRaises(DatabaseException):
self.database.drop_database()
except DatabaseException as e:
if 'Unknown user' in six.text_type(e):
raise unittest.SkipTest('Database user "%s" is not defined' %
username)
else:
raise
finally:
self.database = orig_database
def setUp(self):
self.database = Database('test-db', log_statements=True)
self.database.drop_table(MigrationHistory)
from infi.clickhouse_orm import models, fields, engines
from infi.clickhouse_orm.database import Database
class Test(models.Model):
id = fields.Int64Field()
a = fields.StringField()
b = fields.StringField()
c = fields.StringField()
d = fields.StringField()
engine = engines.MergeTree('id', ('a', 'b', 'c', 'd'))
db_url = 'http://web1:8123'
db_name = 'csv_parser_db'
db_username = '******'
db_password = '******'
db = Database(db_name=db_name, db_url=db_url, username=db_username, password=db_password)
db.create_table(Test)
# Insert some data
db.insert([
Test(id=i, a=str(i), b=str(i), c=str(i), d=str(i)) for i in xrange(10, 15)
])
# Read data
for row in db.select("SELECT * FROM {}.test".format(db_name), model_class=Test):
print row.id, row.a, row.b, row.c, row.d
def setUp(self):
self.database = Database('test-db')
self.database.create_table(Person)
def setUp(self):
self.database = Database('test-db', log_statements=True)
self.database.create_table(ModelWithArrays)
def setUp(self):
self.database = Database('test-db')
self.database.create_table(FixedStringModel)
ts = DateTimeField()
userId = StringField()
sessionId = UInt16Field()
page = FixedStringField(30)
auth = FixedStringField(20)
method = FixedStringField(10)
status = UInt16Field()
level = FixedStringField(10)
itemSession = UInt16Field()
location = NullableField(StringField())
userAgent = NullableField(StringField())
lastname = NullableField(FixedStringField(300))
firstname = NullableField(FixedStringField(300))
registration = UInt64Field()
gender = NullableField(FixedStringField(2))
artist = NullableField(FixedStringField(500))
song = NullableField(StringField())
length = Float32Field()
engine = MergeTree(partition_key=('level', ), order_by=(
'ts',
'userId',
))
database = Database('eventdata', db_url='http://clickhouse:8123')
# database = Database('eventdata')
database.create_table(Events_buf)
database.create_table(Events)
def setUp(self):
self.database = Database('test-db')
def setUp(self):
self.database = Database('test-db', log_statements=True)
def setUp(self):
self.database = Database('test-db')
self.database.create_table(ModelWithMaterializedFields)
def handle(self, *args, **options):
db = Database(CLICKHOUSE_DB_NAME, db_url=CLICKHOUSE_DB_URL, username=CLICKHOUSE_DB_USERNAME,
password=CLICKHOUSE_DB_PASS)
db.drop_database()