Package information:
ClickSQL is a python client for ClickHouse database, which may help users to use ClickHouse more easier and pythonic. More information for ClickHouse can be found at here
pip install ClickSQL
to setup a database connection and send a heartbeat-check signal
from ClickSQL import BaseSingleFactorTableNode
conn_str = "clickhouse://default:[email protected]:8123/system"
Node = BaseSingleFactorTableNode(conn_str)
>>> connection test: Ok.
from ClickSQL import BaseSingleFactorTableNode
conn_str = "clickhouse://default:[email protected]:8123/system"
Node = BaseSingleFactorTableNode(conn_str)
Node('show tables from system limit 1')
>>> connection test: Ok.
>>> name
>>> 0 aggregate_function_combinators
from ClickSQL import BaseSingleFactorTableNode
factor = BaseSingleFactorTableNode(
'clickhouse://default:[email protected]:8123/sample.sample',
cols=['cust_no', 'product_id', 'money'],
order_by_cols=['money asc'],
money='money >= 100000'
)
factor['money'].head(10)
>>> connection test: Ok.
>>> money
>>> 0 1000000.0
>>> 1 1000000.0
>>> 2 1000000.0
>>> 3 1000000.0
>>> 4 1000000.0
>>> 5 1000000.0
>>> 6 1000000.0
>>> 7 1000000.0
>>> 8 1000000.0
>>> 9 1000000.0
insert data into database by various ways
from ClickSQL import BaseSingleFactorTableNode as factortable
import numpy as np
import pandas as pd
factor = factortable( 'clickhouse://default:[email protected]:8123/sample.sample' )
db = 'sample'
table = 'sample'
df = pd.DataFrame(np.random.random(size=(10000,3)),columns=['cust_no', 'product_id', 'money'])
factor.insert_df(df, db, table, chunksize=100000)
from ClickSQL import BaseSingleFactorTableNode as factortable
factor = factortable( 'clickhouse://default:[email protected]:8123/sample.sample' )
factor("insert into sample.sample select * from other_db.other_table")
from ClickSQL import BaseSingleFactorTableNode
conn_str = "clickhouse://default:[email protected]:8123/system"
Node = BaseSingleFactorTableNode(conn_str)
Node('create table test.test2 (v1 String, v2 Int64, v3 Float64,v4 DataTime) Engine=MergeTree() order by v4')
from ClickSQL import BaseSingleFactorTableNode
import numpy as np
import pandas as pd
conn_str = "clickhouse://default:[email protected]:8123/system"
Node = BaseSingleFactorTableNode(conn_str)
db = 'test'
table = 'test2'
df_or_sql_or_dict = pd.DataFrame(np.random.random(size=(10000,2)),columns=['v1', 'v3'])
df_or_sql_or_dict['v2'] =1
df_or_sql_or_dict['v4'] =pd.to_datetime('2020-01-01 00:00:00')
Node.create( db, table, df_or_sql_or_dict, key_cols=['v4'],)
Welcome to improve this package or submit an issue or any others
sn0wfree
- get data from clickhouse
- insert data into clickhouse
- create
- alter
- execute standard SQL and transform into DataFrame(Auto)
- able to execute select query
- able to execute insert query
- no require clickhouse-client
- auto create table sql
- can execute explain query
- Insert Data via DataFrame
- alter function & drop function
- create a pandas_liked executable function, which can compatible with pandas
- distributed query(query insert)
- ORM
- can execute user role query
- create analysis component
- auto report system
- table register system
- data manager system
- meta data manager