Skip to main content

flight_sql_client/
flight_sql_client.rs

1// SPDX-License-Identifier: Apache-2.0
2// SPDX-FileCopyrightText: See the NOTICE file distributed with this work for additional information regarding copyright ownership
3// SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements
4
5//! A command line client for Arrow Flight SQL.
6
7use std::{sync::Arc, time::Duration};
8
9use anyhow::{Context, Result, bail};
10use arrow_array::{ArrayRef, Datum, RecordBatch, StringArray};
11use arrow_cast::{CastOptions, cast_with_options, pretty::pretty_format_batches};
12use arrow_flight::{
13    FlightInfo,
14    flight_service_client::FlightServiceClient,
15    sql::{CommandGetDbSchemas, CommandGetTables, client::FlightSqlServiceClient},
16};
17use arrow_schema::Schema;
18use clap::{Parser, Subcommand, ValueEnum};
19use core::str;
20use futures::TryStreamExt;
21use tonic::{
22    metadata::MetadataMap,
23    transport::{Channel, ClientTlsConfig, Endpoint},
24};
25use tracing_log::log::info;
26
27/// Logging CLI config.
28#[derive(Debug, Parser)]
29pub struct LoggingArgs {
30    /// Log verbosity.
31    ///
32    /// Defaults to "warn".
33    ///
34    /// Use `-v` for "info", `-vv` for "debug", `-vvv` for "trace".
35    ///
36    /// Note you can also set logging level using `RUST_LOG` environment variable:
37    /// `RUST_LOG=debug`.
38    #[clap(
39        short = 'v',
40        long = "verbose",
41        action = clap::ArgAction::Count,
42    )]
43    log_verbose_count: u8,
44}
45
46/// gRPC/HTTP compression algorithms.
47#[derive(Clone, Copy, Debug, PartialEq, Eq, ValueEnum)]
48pub enum CompressionEncoding {
49    Gzip,
50    Deflate,
51    Zstd,
52}
53
54impl From<CompressionEncoding> for tonic::codec::CompressionEncoding {
55    fn from(encoding: CompressionEncoding) -> Self {
56        match encoding {
57            CompressionEncoding::Gzip => Self::Gzip,
58            CompressionEncoding::Deflate => Self::Deflate,
59            CompressionEncoding::Zstd => Self::Zstd,
60        }
61    }
62}
63
64#[derive(Debug, Parser)]
65struct ClientArgs {
66    /// Additional headers.
67    ///
68    /// Can be given multiple times. Headers and values are separated by '='.
69    ///
70    /// Example: `-H foo=bar -H baz=42`
71    #[clap(long = "header", short = 'H', value_parser = parse_key_val)]
72    headers: Vec<(String, String)>,
73
74    /// Username.
75    ///
76    /// Optional. If given, `password` must also be set.
77    #[clap(long, requires = "password")]
78    username: Option<String>,
79
80    /// Password.
81    ///
82    /// Optional. If given, `username` must also be set.
83    #[clap(long, requires = "username")]
84    password: Option<String>,
85
86    /// Auth token.
87    #[clap(long)]
88    token: Option<String>,
89
90    /// Use TLS.
91    ///
92    /// If not provided, use cleartext connection.
93    #[clap(long)]
94    tls: bool,
95
96    /// Dump TLS key log.
97    ///
98    /// The target file is specified by the `SSLKEYLOGFILE` environment variable.
99    ///
100    /// Requires `--tls`.
101    #[clap(long, requires = "tls")]
102    key_log: bool,
103
104    /// Server host.
105    ///
106    /// Required.
107    #[clap(long)]
108    host: String,
109
110    /// Server port.
111    ///
112    /// Defaults to `443` if `tls` is set, otherwise defaults to `80`.
113    #[clap(long)]
114    port: Option<u16>,
115
116    /// Compression accepted by the client for responses sent by the server.
117    ///
118    /// The client will send this information to the server as part of the request. The server is free to pick an
119    /// algorithm from that list or use no compression (called "identity" encoding).
120    ///
121    /// You may define multiple algorithms by using a comma-separated list.
122    #[clap(long, value_delimiter = ',')]
123    accept_compression: Vec<CompressionEncoding>,
124
125    /// Compression of requests sent by the client to the server.
126    ///
127    /// Since the client needs to decide on the compression before sending the request, there is no client<->server
128    /// negotiation. If the server does NOT support the chosen compression, it will respond with an error a la:
129    ///
130    /// ```text
131    /// Ipc error: Status {
132    ///     code: Unimplemented,
133    ///     message: "Content is compressed with `zstd` which isn't supported",
134    ///     metadata: MetadataMap { headers: {"grpc-accept-encoding": "identity", ...} },
135    ///     ...
136    /// }
137    /// ```
138    ///
139    /// Based on the algorithms listed in the `grpc-accept-encoding` header, you may make a more educated guess for
140    /// your next request. Note that `identity` is a synonym for "no compression".
141    #[clap(long)]
142    send_compression: Option<CompressionEncoding>,
143}
144
145#[derive(Debug, Parser)]
146struct Args {
147    /// Logging args.
148    #[clap(flatten)]
149    logging_args: LoggingArgs,
150
151    /// Client args.
152    #[clap(flatten)]
153    client_args: ClientArgs,
154
155    #[clap(subcommand)]
156    cmd: Command,
157}
158
159/// Different available commands.
160#[derive(Debug, Subcommand)]
161enum Command {
162    /// Get catalogs.
163    Catalogs,
164    /// Get db schemas for a catalog.
165    DbSchemas {
166        /// Name of a catalog.
167        ///
168        /// Required.
169        catalog: String,
170        /// Specifies a filter pattern for schemas to search for.
171        /// When no schema_filter is provided, the pattern will not be used to narrow the search.
172        /// In the pattern string, two special characters can be used to denote matching rules:
173        ///     - "%" means to match any substring with 0 or more characters.
174        ///     - "_" means to match any one character.
175        #[clap(short, long)]
176        db_schema_filter: Option<String>,
177    },
178    /// Get tables for a catalog.
179    Tables {
180        /// Name of a catalog.
181        ///
182        /// Required.
183        catalog: String,
184        /// Specifies a filter pattern for schemas to search for.
185        /// When no schema_filter is provided, the pattern will not be used to narrow the search.
186        /// In the pattern string, two special characters can be used to denote matching rules:
187        ///     - "%" means to match any substring with 0 or more characters.
188        ///     - "_" means to match any one character.
189        #[clap(short, long)]
190        db_schema_filter: Option<String>,
191        /// Specifies a filter pattern for tables to search for.
192        /// When no table_filter is provided, all tables matching other filters are searched.
193        /// In the pattern string, two special characters can be used to denote matching rules:
194        ///     - "%" means to match any substring with 0 or more characters.
195        ///     - "_" means to match any one character.
196        #[clap(short, long)]
197        table_filter: Option<String>,
198        /// Specifies a filter of table types which must match.
199        /// The table types depend on vendor/implementation. It is usually used to separate tables from views or system tables.
200        /// TABLE, VIEW, and SYSTEM TABLE are commonly supported.
201        #[clap(long)]
202        table_types: Vec<String>,
203    },
204    /// Get table types.
205    TableTypes,
206
207    /// Execute given statement.
208    StatementQuery {
209        /// SQL query.
210        ///
211        /// Required.
212        query: String,
213    },
214
215    /// Prepare given statement and then execute it.
216    PreparedStatementQuery {
217        /// SQL query.
218        ///
219        /// Required.
220        ///
221        /// Can contains placeholders like `$1`.
222        ///
223        /// Example: `SELECT * FROM t WHERE x = $1`
224        query: String,
225
226        /// Additional parameters.
227        ///
228        /// Can be given multiple times. Names and values are separated by '='. Values will be
229        /// converted to the type that the server reported for the prepared statement.
230        ///
231        /// Example: `-p $1=42`
232        #[clap(short, value_parser = parse_key_val)]
233        params: Vec<(String, String)>,
234    },
235}
236
237#[tokio::main]
238async fn main() -> Result<()> {
239    let args = Args::parse();
240    setup_logging(args.logging_args)?;
241    let mut client = setup_client(args.client_args)
242        .await
243        .context("setup client")?;
244
245    let flight_info = match args.cmd {
246        Command::Catalogs => client.get_catalogs().await.context("get catalogs")?,
247        Command::DbSchemas {
248            catalog,
249            db_schema_filter,
250        } => client
251            .get_db_schemas(CommandGetDbSchemas {
252                catalog: Some(catalog),
253                db_schema_filter_pattern: db_schema_filter,
254            })
255            .await
256            .context("get db schemas")?,
257        Command::Tables {
258            catalog,
259            db_schema_filter,
260            table_filter,
261            table_types,
262        } => client
263            .get_tables(CommandGetTables {
264                catalog: Some(catalog),
265                db_schema_filter_pattern: db_schema_filter,
266                table_name_filter_pattern: table_filter,
267                table_types,
268                // Schema is returned as ipc encoded bytes.
269                // We do not support returning the schema as there is no trivial mechanism
270                // to display the information to the user.
271                include_schema: false,
272            })
273            .await
274            .context("get tables")?,
275        Command::TableTypes => client.get_table_types().await.context("get table types")?,
276        Command::StatementQuery { query } => client
277            .execute(query, None)
278            .await
279            .context("execute statement")?,
280        Command::PreparedStatementQuery { query, params } => {
281            let mut prepared_stmt = client
282                .prepare(query, None)
283                .await
284                .context("prepare statement")?;
285
286            if !params.is_empty() {
287                prepared_stmt
288                    .set_parameters(
289                        construct_record_batch_from_params(
290                            &params,
291                            prepared_stmt
292                                .parameter_schema()
293                                .context("get parameter schema")?,
294                        )
295                        .context("construct parameters")?,
296                    )
297                    .context("bind parameters")?;
298            }
299
300            prepared_stmt
301                .execute()
302                .await
303                .context("execute prepared statement")?
304        }
305    };
306
307    let batches = execute_flight(&mut client, flight_info)
308        .await
309        .context("read flight data")?;
310
311    let res = pretty_format_batches(batches.as_slice()).context("format results")?;
312    println!("{res}");
313
314    Ok(())
315}
316
317async fn execute_flight(
318    client: &mut FlightSqlServiceClient<Channel>,
319    info: FlightInfo,
320) -> Result<Vec<RecordBatch>> {
321    let schema = Arc::new(Schema::try_from(info.clone()).context("valid schema")?);
322    let mut batches = Vec::with_capacity(info.endpoint.len() + 1);
323    batches.push(RecordBatch::new_empty(schema));
324    info!("decoded schema");
325
326    for endpoint in info.endpoint {
327        let Some(ticket) = &endpoint.ticket else {
328            bail!("did not get ticket");
329        };
330
331        let mut flight_data = client.do_get(ticket.clone()).await.context("do get")?;
332        log_metadata(flight_data.headers(), "header");
333
334        let mut endpoint_batches: Vec<_> = (&mut flight_data)
335            .try_collect()
336            .await
337            .context("collect data stream")?;
338        batches.append(&mut endpoint_batches);
339
340        if let Some(trailers) = flight_data.trailers() {
341            log_metadata(&trailers, "trailer");
342        }
343    }
344    info!("received data");
345
346    Ok(batches)
347}
348
349fn construct_record_batch_from_params(
350    params: &[(String, String)],
351    parameter_schema: &Schema,
352) -> Result<RecordBatch> {
353    let mut items = Vec::<(&String, ArrayRef)>::new();
354
355    for (name, value) in params {
356        let field = parameter_schema.field_with_name(name)?;
357        let value_as_array = StringArray::new_scalar(value);
358        let casted = cast_with_options(
359            value_as_array.get().0,
360            field.data_type(),
361            &CastOptions::default(),
362        )?;
363        items.push((name, casted))
364    }
365
366    Ok(RecordBatch::try_from_iter(items)?)
367}
368
369fn setup_logging(args: LoggingArgs) -> Result<()> {
370    use tracing_subscriber::{EnvFilter, FmtSubscriber, util::SubscriberInitExt};
371
372    tracing_log::LogTracer::init().context("tracing log init")?;
373
374    let filter = match args.log_verbose_count {
375        0 => "warn",
376        1 => "info",
377        2 => "debug",
378        _ => "trace",
379    };
380    let filter = EnvFilter::try_new(filter).context("set up log env filter")?;
381
382    let subscriber = FmtSubscriber::builder().with_env_filter(filter).finish();
383    subscriber.try_init().context("init logging subscriber")?;
384
385    Ok(())
386}
387
388async fn setup_client(args: ClientArgs) -> Result<FlightSqlServiceClient<Channel>> {
389    let port = args.port.unwrap_or(if args.tls { 443 } else { 80 });
390
391    let protocol = if args.tls { "https" } else { "http" };
392
393    let mut endpoint = Endpoint::new(format!("{}://{}:{}", protocol, args.host, port))
394        .context("create endpoint")?
395        .connect_timeout(Duration::from_secs(20))
396        .timeout(Duration::from_secs(20))
397        .tcp_nodelay(true) // Disable Nagle's Algorithm since we don't want packets to wait
398        .tcp_keepalive(Option::Some(Duration::from_secs(3600)))
399        .http2_keep_alive_interval(Duration::from_secs(300))
400        .keep_alive_timeout(Duration::from_secs(20))
401        .keep_alive_while_idle(true);
402
403    if args.tls {
404        let mut tls_config = ClientTlsConfig::new().with_enabled_roots();
405        if args.key_log {
406            tls_config = tls_config.use_key_log();
407        }
408
409        endpoint = endpoint
410            .tls_config(tls_config)
411            .context("create TLS endpoint")?;
412    }
413
414    let channel = endpoint.connect().await.context("connect to endpoint")?;
415
416    let mut client = FlightServiceClient::new(channel);
417    for encoding in args.accept_compression {
418        client = client.accept_compressed(encoding.into());
419    }
420    if let Some(encoding) = args.send_compression {
421        client = client.send_compressed(encoding.into());
422    }
423    let mut client = FlightSqlServiceClient::new_from_inner(client);
424    info!("connected");
425
426    for (k, v) in args.headers {
427        client.set_header(k, v);
428    }
429
430    if let Some(token) = args.token {
431        client.set_token(token);
432        info!("token set");
433    }
434
435    match (args.username, args.password) {
436        (None, None) => {}
437        (Some(username), Some(password)) => {
438            client
439                .handshake(&username, &password)
440                .await
441                .context("handshake")?;
442            info!("performed handshake");
443        }
444        (Some(_), None) => {
445            bail!("when username is set, you also need to set a password")
446        }
447        (None, Some(_)) => {
448            bail!("when password is set, you also need to set a username")
449        }
450    }
451
452    Ok(client)
453}
454
455/// Parse a single key-value pair
456fn parse_key_val(s: &str) -> Result<(String, String), String> {
457    let pos = s
458        .find('=')
459        .ok_or_else(|| format!("invalid KEY=value: no `=` found in `{s}`"))?;
460    Ok((s[..pos].to_owned(), s[pos + 1..].to_owned()))
461}
462
463/// Log headers/trailers.
464fn log_metadata(map: &MetadataMap, what: &'static str) {
465    for k_v in map.iter() {
466        match k_v {
467            tonic::metadata::KeyAndValueRef::Ascii(k, v) => {
468                info!(
469                    "{}: {}={}",
470                    what,
471                    k.as_str(),
472                    v.to_str().unwrap_or("<invalid>"),
473                );
474            }
475            tonic::metadata::KeyAndValueRef::Binary(k, v) => {
476                info!(
477                    "{}: {}={}",
478                    what,
479                    k.as_str(),
480                    String::from_utf8_lossy(v.as_ref()),
481                );
482            }
483        }
484    }
485}