SQL programming
Project description
ClickSQL: ClickHouse client for Humans
Package information:
ClickSQL is a smart client for ClickHouse database, which may help users to use ClickHouse more easier and smoother.
more information for ClickHouse can be found at here
Installation
pip install ClickSQL
Usage
Initial connection
to setup a database connection and send a heartbeat-check signal
from ClickSQL import BaseSingleFactorTableNode
conn_str = "clickhouse://default:test121231@99.99.9.9:8123/system"
Node = BaseSingleFactorTableNode(conn_str)
>>> connection test: Ok.
Query
execute a SQL Query
from ClickSQL import BaseSingleFactorTableNode
conn_str = "clickhouse://default:test121231@99.99.9.9:8123/system"
Node = BaseSingleFactorTableNode(conn_str)
Node('show tables from system limit 1')
>>> connection test: Ok.
>>> name
>>> 0 aggregate_function_combinators
execute a Query without SQL
from ClickSQL import BaseSingleFactorTableNode
factor = BaseSingleFactorTableNode(
'clickhouse://default:default@127.0.0.1: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
insert data into database by various ways
Insert data via DataFrame
from ClickSQL import BaseSingleFactorTableNode as factortable
import numpy as np
import pandas as pd
factor = factortable( 'clickhouse://default:default@127.0.0.1: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)
Insert data via SQL(Inner)
from ClickSQL import BaseSingleFactorTableNode as factortable
factor = factortable( 'clickhouse://default:default@127.0.0.1:8123/sample.sample' )
factor("insert into sample.sample select * from other_db.other_table")
Create table
Create table by SQL
from ClickSQL import BaseSingleFactorTableNode
conn_str = "clickhouse://default:test121231@99.99.9.9:8123/system"
Node = BaseSingleFactorTableNode(conn_str)
Node('create table test.test2 (v1 String, v2 Int64, v3 Float64,v4 DataTime) Engine=MergeTree() order by v4')
Create table by DataFrame
from ClickSQL import BaseSingleFactorTableNode
import numpy as np
import pandas as pd
conn_str = "clickhouse://default:test121231@99.99.9.9: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'],)
Contribution
there is welcome to do more work to improve this package more convenient
Author
sn0wfree
functions
- get data from clickhouse
- insert data into clickhouse
- create
- alter
Plan
Available function
- access clickhouse service
- execute standard SQL and transform into dataframe
- able to execute select query
- able to execute insert query
- no require clickhouse-client
- auto create table sql
- can execute explain query
schedule
- ORM
- create a pandas_liked executable function, which can compatible with pandas
- alter function & drop function
- can execute user role query
- create analysis component
- auto report system
- table register system
- data manager system
- meta data manager
- distributed query(query+insert)
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
ClickSQL-0.1.8.8.tar.gz
(36.0 kB
view hashes)
Built Distribution
ClickSQL-0.1.8.8-py3-none-any.whl
(45.9 kB
view hashes)
Close
Hashes for ClickSQL-0.1.8.8-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 4b3a483b6b614c09dcb9229bcec632d3d488a1be56febd288223f53223134e52 |
|
MD5 | 4bdb503379a5d77ccbaabe543f6d5188 |
|
BLAKE2b-256 | ae8db39f4e4c91404853976901ce8e6d92bea1c0425073224c8b845f26108c31 |