datafuselabs / bendsql Goto Github PK
View Code? Open in Web Editor NEWDatabend Native Client
License: Apache License 2.0
Databend Native Client
License: Apache License 2.0
root@localhost:8000/> select 'a\nb' as a, hex(a);
SELECT
'a\nb' AS a,
hex(a)
┌─────────────────┐
│ a │ hex(a) │
│ String │ String │
├────────┼────────┤
│ a │ 610a62 │
│ b │ │
└─────────────────┘
1 row in 0.007 sec. Processed 1 rows, 1B (149.15 rows/s, 149B/s)
mysql client:
mysql> select 'a\nb' as a, hex(a);
+------+--------+
| a | hex(a) |
+------+--------+
| a
b | 610a62 |
+------+--------+
1 row in set (0.00 sec)
Read 1 rows, 1.00 B in 0.001 sec., 999.27 rows/sec., 999.27 B/sec.
Summary
bendsql upload file to stage
CREATE stage tmp_stage url = 's3://mystage' connection =(
endpoint_url = 'http://192.168.1.100:9900' access_key_id = 'minioadmin' secret_access_key = 'minioadmin'
)
list @tmp_stage; -> ok
put fs:///xxxx @tmp_stage;
APIError: ParsingError: Invalid stage location: @tmp_stage
But a month ago this feature was ok.
docs: https://databend.rs/doc/load-data/stage/stage-files#uploading-with-put-command
currently the bendsql is released as source code in brew, however it takes actually a long time to actually compiles it in local machines.
how about making a brew cask release which allows install binary directly?
Summary
bendsql upload file to stage
CREATE stage tmp_stage url = 's3://mystage' connection =(
endpoint_url = 'http://192.168.1.100:9900' access_key_id = 'minioadmin' secret_access_key = 'minioadmin'
)
list @tmp_stage; -> ok
put fs:///xxxx @tmp_stage;
APIError: ParsingError: Invalid stage location: @tmp_stage
image
But a month ago this feature was ok.
docs: https://databend.rs/doc/load-data/stage/stage-files#uploading-with-put-command
> show indexes;
SHOW INDEXES
error happens after fetched 0 rows: ParseError: input contains invalid characters
>
show create table ontime;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Table │ Create Table │
│ String │ String │
├────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ ontime │ CREATE TABLE `ontime` (\n `Year` SMALLINT UNSIGNED,\n `Quarter` TINYINT UNSIGNED,\n `Month` TINYINT UNSIGNED,\n `DayofMonth` TINYINT UNSIGNED,\n `DayOfWeek` TIN... │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 row result in 0.550 sec. Processed 0 rows, 0 B (0 rows/s, 0 B/s)
bendsql> presign upload @~/a.csv;
presign upload @ ~ / a.csv
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ method │ headers │ url │
│ String │ Variant │ String │
├────────┼──────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ PUT │ {"host":"s3.us-ea... │ https://s3.us-east-2.amazonaws.com/query-storage-53b9412/tn3ftqihs/stage/user/cloudapp/a.csv?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential... │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 row result in 1.332 sec. Processed 0 rows, 0 B (0 rows/s, 0 B/s)
want display result in multi rows.
We don't need open any new terminal window for bendsql if we support:
show warehouses;
... ...
use wearehouse 'xx';
Suspend the warehouse with bendsql.
Variant field is json in REST API, but not in FlightSQL.
bendsql> select avg(number) from numbers(10);
┌───────────────────┐
│ avg(number) │
│ Nullable(Float64) │
├───────────────────┤
│ 4.5 │
└───────────────────┘
to
┌───────────────────┐
│ avg(number) │
│ Nullable(Float64) │
├───────────────────┤
│ 4.5│
└───────────────────┘
It's more simple and general for default config path.
Query:
-- TPC-H 1
select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= add_days(to_date('1998-12-01'), -90) group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus;
Error:
❯ bendsql -q "-- TPC-H 1
select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= add_days(to_date('1998-12-01'), -90) group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus;" --time
error: unexpected argument '-- TPC-H 1
select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <' found
currently on connecting warehosues from databend cloud, if the resource is still in scheduling, it will return a timeout error. this error is actually retriable.
bendsql> SELECT avg(number) FROM numbers(1e9) group by number%5;
SELECT
avg(number)
FROM
numbers(1 e9)
GROUP BY
number % 5;
┌───────────────────┐
│ avg(number) │
│ Nullable(Float64) │
├───────────────────┤
│ 499999997.5 │
│ 499999998.5 │
│ 499999999.5 │
│ 500000000.5 │
│ 500000001.5 │
└───────────────────┘
5 rows in 2.638 sec. Processed 0 rows, 0B (0 rows/s, 0B/s)
bendsql> create table t2 storage_format = 'native' compression = 'lz4' as SELECT number FROM numbers(200000000);
200000000 rows affected in (32.957 sec)
bendsql> SELECT avg(number) FROM t2 group by number%5;
SELECT
avg(number)
FROM
t2
GROUP BY
number % 5;
┌───────────────────┐
│ avg(number) │
│ Nullable(Float64) │
├───────────────────┤
│ 99999997.5 │
│ 99999998.5 │
│ 99999999.5 │
│ 100000000.5 │
│ 100000001.5 │
└───────────────────┘
5 rows in 1.266 sec. Processed 0 rows, 0B (0 rows/s, 0B/s)
clickhouse
localhost.localdomain :) SELECT avg(number) FROM numbers_mt(1e9) group by number%5;
SELECT avg(number)
FROM numbers_mt(1000000000.)
GROUP BY number % 5
Query id: 50e19812-8edb-47ec-81a4-12165e9a5244
┌─avg(number)─┐
│ 499999997.5 │
│ 499999998.5 │
│ 499999999.5 │
│ 500000000.5 │
│ 500000001.5 │
└─────────────┘
5 rows in set. Elapsed: 0.929 sec. Processed 989.11 million rows, 7.91 GB (1.06 billion rows/s., 8.51 GB/s.)
how to reproduce:
root@localhost:8000/d1> SELECT -0.000000000000000000000000000000000673299310, [], minus(a), to_bitmap(837), to_bitmap(526) FROM test GROUP BY GROUPING SETS (
(-0.000000000000000000000000000000000673299310, [], minus(a), to_bitmap(837), to_bitmap(526)));
ParseError: Unknown type: TypeDesc { name: "Nothing", args: [] }
When creating a non-existed table or dropping an existed table, the client will panic.
Howerever, the creating/dropping is successful
> CREATE TABLE t1(a FLOAT, b INTEGER);
thread 'main' panicked at 'called `Option::unwrap()` on a `None` value', $HOME/.cargo/registry/src/mirrors.ustc.edu.cn-12df342d903acd47/arrow-flight-36.0.0/src/sql/client.rs:194:55
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
> DROP TABLE t1;
thread 'main' panicked at 'called `Option::unwrap()` on a `None` value', $HOME/.cargo/registry/src/mirrors.ustc.edu.cn-12df342d903acd47/arrow-flight-36.0.0/src/sql/client.rs:194:55
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
╰─$ brew install databendcloud/homebrew-tap/bendsql
==> Tapping databendcloud/tap
Cloning into '/opt/homebrew/Library/Taps/databendcloud/homebrew-tap'...
remote: Enumerating objects: 37, done.
remote: Counting objects: 100% (37/37), done.
remote: Compressing objects: 100% (34/34), done.
remote: Total 37 (delta 20), reused 5 (delta 2), pack-reused 0
Receiving objects: 100% (37/37), 11.42 KiB | 2.28 MiB/s, done.
Resolving deltas: 100% (20/20), done.
Tapped 1 formula (14 files, 28.3KB).
Warning: You are using macOS 13.
We do not provide support for this pre-release version.
You will encounter build failures with some formulae.
Please create pull requests instead of asking for help on Homebrew's GitHub,
Twitter or any other official channels. You are responsible for resolving
any issues you experience while you are running this
pre-release version.
==> Downloading https://ghcr.io/v2/homebrew/core/libssh2/manifests/1.10.0
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/libssh2/blobs/sha256:f9dab718cfa591fa90dc716a337e4c2c1da2db651b669565c3cc08e6a6074
==> Downloading from https://pkg-containers.githubusercontent.com/ghcr1/blobs/sha256:f9dab718cfa591fa90dc716a337e4c2c1da2db651b6695
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/rust/manifests/1.62.1
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/rust/blobs/sha256:a559acc618a575bd0e05b3a7f0dc5317556a77cd61ee71caf09eb7e79bf54c01
==> Downloading from https://pkg-containers.githubusercontent.com/ghcr1/blobs/sha256:a559acc618a575bd0e05b3a7f0dc5317556a77cd61ee71
######################################################################## 100.0%
==> Downloading https://github.com/datafuselabs/databend-client/archive/v0.2.6.tar.gz
==> Downloading from https://codeload.github.com/datafuselabs/databend-client/tar.gz/refs/tags/v0.2.6
-#O#- # #
==> Installing bendsql from databendcloud/tap
==> Installing dependencies for databendcloud/tap/bendsql: libssh2 and rust
==> Installing databendcloud/tap/bendsql dependency: libssh2
==> Pouring libssh2--1.10.0.arm64_monterey.bottle.tar.gz
🍺 /opt/homebrew/Cellar/libssh2/1.10.0: 184 files, 1MB
==> Installing databendcloud/tap/bendsql dependency: rust
==> Pouring rust--1.62.1.arm64_monterey.bottle.tar.gz
🍺 /opt/homebrew/Cellar/rust/1.62.1: 35,381 files, 849.4MB
==> Installing databendcloud/tap/bendsql
==> cargo install
Last 15 lines from /Users/yazhou/Library/Logs/Homebrew/bendsql/01.cargo:
Downloaded idna v0.3.0
Downloaded http-body v0.4.5
Downloaded hashbrown v0.13.2
Downloaded half v2.2.1
Downloaded http v0.2.9
Downloaded futures v0.3.28
Downloaded sync_wrapper v0.1.2
Downloaded heck v0.4.1
Downloaded hashbrown v0.12.3
Downloaded dyn-clone v1.0.11
Downloaded crossterm v0.25.0
error: failed to compile `bendsql v0.2.6 (/private/tmp/bendsql-20230421-30276-jh62y1/databend-client-0.2.6/cli)`, intermediate artifacts can be found at `/private/tmp/bendsql-20230421-30276-jh62y1/databend-client-0.2.6/target`
Caused by:
package `clap_derive v4.2.0` cannot be built because it requires rustc 1.64.0 or newer, while the currently active rustc version is 1.62.1
Do not report this issue to Homebrew/brew or Homebrew/core!
Error: You are using macOS 13.
We do not provide support for this pre-release version.
You will encounter build failures with some formulae.
Please create pull requests instead of asking for help on Homebrew's GitHub,
Twitter or any other official channels. You are responsible for resolving
any issues you experience while you are running this
pre-release version.
however when I view my version of rustc, it shows it's 1.66.0:
╰─$ rustc --version 1 ↵
rustc 1.66.0-nightly (8b0c05d9a 2022-10-07)
FlightSQL support nested datatypes
Currently, only the explain statement displays the escaped newline character. This results in a query string result not being displayed correctly, especially for functions like json_pretty
that almost always have multiple lines of results.
For example:
root@127.0.0.1:8000/default> select json_pretty(parse_json('[1,2,[4,5,6,{"k":"v"}]]'));
SELECT
json_pretty(parse_json('[1,2,[4,5,6,{"k":"v"}]]'))
┌──────────────────────────────────────────────────────────────────────────────────┐
│ json_pretty(parse_json('[1,2,[4,5,6,{"k":"v"}]]')) │
│ String │
├──────────────────────────────────────────────────────────────────────────────────┤
│ [\n 1,\n 2,\n [\n 4,\n 5,\n 6,\n {\n "k": "v"\n }\n ]\n] │
└──────────────────────────────────────────────────────────────────────────────────┘
1 row result in 0.067 sec. Processed 1 rows, 1B (14.89 rows/s, 14B/s)
When use the databend-driver
with poem
, it shows this error
error: future cannot be sent between threads safely
--> src\api\query.rs:18:1
|
18 | #[handler]
| ^^^^^^^^^^ future created by async block is not `Send`
|
= help: the trait `Send` is not implemented for `(dyn Future<Output = Result<databend_client::response::QueryResponse, anyhow::Error>> + 'static)`
note: future is not `Send` as this value is used across an await
--> src\db\db.rs:129:42
|
128 | let mut rows = self.conn.query_iter(&sql).await?;
| -------- has type `databend_driver::rows::RowIterator` which is not `Send`
129 | while let Some(row) = rows.next().await {
| ^^^^^^ await occurs here, with `mut rows` maybe used later
...
135 | }
| - `mut rows` is later dropped here
Any advice?
arch :) select number, number + 3 from numbers(4) \G
Row 1:
──────
number: 0
plus(number, 3): 3
Row 2:
──────
number: 1
plus(number, 3): 4
Row 3:
──────
number: 2
plus(number, 3): 5
Row 4:
──────
number: 3
plus(number, 3): 6
loading auto complete keywords failed: APIError: ResponseError with 2601: , cause: failed to decode Protobuf message: buffer underflow
(while get UDFs).
bendsql --version
bendsql 0.9.1-homebrew
bendsql> copy into supplier from @tpch_data2/tpch_100/supplier/ pattern='.*[.]parquet' file_format=(type=parquet);
error: APIError: ResponseError with 1005: error:
--> SQL:3:15
|
1 | copy INTO supplier
2 | FROM
3 | @tpch_data2 / tpch_100 / supplier / pattern = '.*[.]parquet' file_format =(TYPE = parquet)
| ^ expected `FILES`, `PATTERN`, `FILE_FORMAT`, `VALIDATION_MODE`, `SIZE_LIMIT`, `MAX_FILES`, `SPLIT_SIZE`, `PURGE`, `FORCE`, `ON_ERROR`, `DISABLE_VARIANT_CHECK`, `RETURN_FAILED_ONLY`, `FORMAT`, or `;`
bendsql> list @tpch_data2/tpch_100/;
error: APIError: ResponseError with 1005: error:
--> SQL:1:18
|
1 | list @tpch_data2 / tpch_100 /
| ^ expected `PATTERN`, `FORMAT`, or `;`
BendSQL supports completion with the query when connection:
SELECT name FROM system.tables UNION ALL SELECT name FROM system.columns UNION ALL SELECT name FROM system.databases UNION ALL SELECT name FROM system.functions
But the server may return error:
Connected to DatabendQuery v1.2.55-nightly-a74f88a1b603390742a536ba6cde1683ab1f9cd6(rust-1.72.0-nightly-2023-08-10T07:56:10.137121045Z)
thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: Api(InvalidResponse(QueryError { code: 1025, message: "Unknown table '_airbyte_raw_simple_stream_with_namespace_resulting_into_long_names'" }))', cli/src/session.rs:69:62
Then the BendSQL will not work anymore.
We should ignore the completion if the init phase something wrong.
change to
❯ ps -ef | grep bendsql
everpcpc 1338305 1337414 0 19:01 pts/28 00:00:00 bendsql -h xxx.databend.com -u everpcpc -P 443 -p xxxxxxxx --tls
see https://github.com/datafuselabs/bendsql/blob/main/core/src/client.rs#L118-L121, when use
"DATABEND_DSN="databend://root:@localhost:8000/?sslmode=false&enable_experimental_merge_into=1" cargo run -r -- 3000 2>&1 | tee rr.log", we can't work.
Databend benchmark TPC-H CI can be change to the --servertime
instead of client time.
❯ bendsql
Welcome to BendSQL 0.3.9-fea6039(2023-05-16T15:35:52.365502375Z).
Trying connect to localhost:8000 as user root.
Connected to DatabendQuery v1.1.33-nightly-7b4b3399dc08c10ee4d4a01b3e08c135ae8c0406(rust-1.70.0-nightly-2023-06-14T03:07:48.718011874Z)
databend :) select * from aa;
SELECT
*
FROM
aa
┌────────────────────────────────────┐
│ a │ b │
│ Int32 │ Timestamp │
├───────┼────────────────────────────┤
│ 3 │ 2023-06-15 02:28:43.049592 │
│ 3 │ 2023-06-15 02:28:35.255629 │
│ 3 │ 2023-06-15 02:28:34.772536 │
│ 3 │ 2023-06-15 02:28:34.144613 │
└────────────────────────────────────┘
4 rows in 0.061 sec. Processed 4 rows, 4B (65.04 rows/s, 2.03 KiB/s)
databend :) select $1 from aa;
>
Decimal256 display padding zero is missed.
root@localhost:18000/default> select 0.0000000000000000000000000000000001234;
SELECT
0.0000000000000000000000000000000001234
┌─────────────────────────────────────────┐
│ 0.0000000000000000000000000000000001234 │
│ Decimal(37, 37) │
├─────────────────────────────────────────┤
│ 0.0000000000000000000000000000000001234 │
└─────────────────────────────────────────┘
1 row read in 0.020 sec. Processed 1 row, 1 B (51.04 row/s, 51 B/s)
root@localhost:18000/default> select 0.0000000000000000000000000000000000001234;
SELECT
0.0000000000000000000000000000000000001234
┌────────────────────────────────────────────┐
│ 0.0000000000000000000000000000000000001234 │
│ Decimal(40, 40) │
├────────────────────────────────────────────┤
│ 0.1234 │
└────────────────────────────────────────────┘
1 row read in 0.019 sec. Processed 1 row, 1 B (51.46 row/s, 51 B/s)
maybe we can take a similiar api like databrik-sql-nodejs:
const result = await operation.fetchAll({
progress: true,
callback: (stateResponse) => {
console.log(stateResponse.taskStatus);
},
});
https://github.com/databricks/databricks-sql-nodejs/blob/main/docs/readme.md
PRESIGN is prefered on uploading big files on a remote server, it may save lots of cost on the bandwidth.
however, we still need a option to allow upload via stream upload when the backend storage did not offer PRESIGN support (like HDFS)
BendSQL 0.4.7-dev
USE warehouse 'test-medium'
deploy@(test-medium)/> use bohu;
USE bohu
0 row in 8.618 sec. Processed 0 rows, 0B (0 rows/s, 0B/s)
deploy@(test-medium)/> show tables;
SHOW TABLES
┌───────────────────────────────────────┐
│ Tables_in_default │
│ String │
├───────────────────────────────────────┤
│ _airbyte_raw_airbyteontimetest │
│ _airbyte_raw_append_stream │
│ _airbyte_raw_arrays │
│ · │
│ · │
│ · │
│ users__dbt_tmp │
│ 185 rows │
│ (40 shown) │
└───────────────────────────────────────┘
185 rows in 7.976 sec. Processed 185 rows, 185B (23.19 rows/s, 4.87 KiB/s)
deploy@(test-medium)/> use bohu;
USE bohu
0 row in 0.508 sec. Processed 0 rows, 0B (0 rows/s, 0B/s)
deploy@(test-medium)/bohu> show tables;
SHOW TABLES
┌────────────────┐
│ Tables_in_bohu │
│ String │
├────────────────┤
│ c │
│ c_random │
│ employees │
│ hits │
│ hits_1 │
│ hits_2 │
│ hits_raw │
│ random_source │
│ student │
│ t1 │
│ t2 │
│ test_order │
└────────────────┘
12 rows in 0.551 sec. Processed 12 rows, 12B (21.78 rows/s, 4.41 KiB/s)
like clickhouse client -mA
https://github.com/datafuselabs/bendsql/blob/main/cli/src/config.rs#L34-L41
cat ~/.config/bendsql/config.toml
[settings]
max_display_rows=1000
max_width=10000
max_col_width=1000
use:
show settings;
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.