|
| 1 | +// Licensed to the Apache Software Foundation (ASF) under one |
| 2 | +// or more contributor license agreements. See the NOTICE file |
| 3 | +// distributed with this work for additional information |
| 4 | +// regarding copyright ownership. The ASF licenses this file |
| 5 | +// to you under the Apache License, Version 2.0 (the |
| 6 | +// "License"); you may not use this file except in compliance |
| 7 | +// with the License. You may obtain a copy of the License at |
| 8 | +// |
| 9 | +// http://www.apache.org/licenses/LICENSE-2.0 |
| 10 | +// |
| 11 | +// Unless required by applicable law or agreed to in writing, |
| 12 | +// software distributed under the License is distributed on an |
| 13 | +// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| 14 | +// KIND, either express or implied. See the License for the |
| 15 | +// specific language governing permissions and limitations |
| 16 | +// under the License. |
| 17 | + |
| 18 | +use arrow::array::Date32Array; |
| 19 | +use datafusion::arrow::array::StringArray; |
| 20 | +use datafusion::arrow::datatypes::{DataType, Field, Schema}; |
| 21 | +use datafusion::arrow::record_batch::RecordBatch; |
| 22 | +use datafusion::assert_batches_eq; |
| 23 | +use datafusion::error::Result; |
| 24 | +use datafusion::prelude::*; |
| 25 | +use std::sync::Arc; |
| 26 | + |
| 27 | +/// This example demonstrates how to use the to_char function via sql |
| 28 | +/// |
| 29 | +/// This function accepts date, time, timestamp and duration values |
| 30 | +/// in the first argument and string values for the second |
| 31 | +#[tokio::main] |
| 32 | +async fn main() -> Result<()> { |
| 33 | + let schema = Arc::new(Schema::new(vec![ |
| 34 | + Field::new("values", DataType::Date32, false), |
| 35 | + Field::new("patterns", DataType::Utf8, false), |
| 36 | + ])); |
| 37 | + |
| 38 | + let batch = RecordBatch::try_new( |
| 39 | + schema, |
| 40 | + vec![ |
| 41 | + Arc::new(Date32Array::from(vec![18506, 18507, 18508, 18509])), |
| 42 | + Arc::new(StringArray::from(vec![ |
| 43 | + "%Y-%m-%d", "%Y:%m:%d", "%Y%m%d", "%d-%m-%Y", |
| 44 | + ])), |
| 45 | + ], |
| 46 | + )?; |
| 47 | + |
| 48 | + // declare a new context. In spark API, this corresponds to a new spark SQLsession |
| 49 | + let ctx = SessionContext::new(); |
| 50 | + |
| 51 | + // declare a table in memory. In spark API, this corresponds to createDataFrame(...). |
| 52 | + ctx.register_batch("t", batch)?; |
| 53 | + let _ = ctx.table("t").await?; |
| 54 | + |
| 55 | + // use to_char function to convert col 'values' to timestamp type using |
| 56 | + // patterns stored in col 'patterns' |
| 57 | + let result = ctx |
| 58 | + .sql("SELECT to_char(values, patterns) from t") |
| 59 | + .await? |
| 60 | + .collect() |
| 61 | + .await?; |
| 62 | + |
| 63 | + assert_batches_eq!( |
| 64 | + &[ |
| 65 | + "+------------------------------+", |
| 66 | + "| to_char(t.values,t.patterns) |", |
| 67 | + "+------------------------------+", |
| 68 | + "| 2020-09-01 |", |
| 69 | + "| 2020:09:02 |", |
| 70 | + "| 20200903 |", |
| 71 | + "| 04-09-2020 |", |
| 72 | + "+------------------------------+", |
| 73 | + ], |
| 74 | + &result |
| 75 | + ); |
| 76 | + |
| 77 | + // the date_format alias for the to_char function can be used as well |
| 78 | + let result = ctx |
| 79 | + .sql("SELECT date_format(values, patterns) from t") |
| 80 | + .await? |
| 81 | + .collect() |
| 82 | + .await?; |
| 83 | + |
| 84 | + assert_batches_eq!( |
| 85 | + &[ |
| 86 | + "+------------------------------+", |
| 87 | + "| to_char(t.values,t.patterns) |", |
| 88 | + "+------------------------------+", |
| 89 | + "| 2020-09-01 |", |
| 90 | + "| 2020:09:02 |", |
| 91 | + "| 20200903 |", |
| 92 | + "| 04-09-2020 |", |
| 93 | + "+------------------------------+", |
| 94 | + ], |
| 95 | + &result |
| 96 | + ); |
| 97 | + |
| 98 | + // use to_char function to convert col 'values' with a fixed format |
| 99 | + let result = ctx |
| 100 | + .sql("SELECT to_char(values, '%m-%d-%Y') FROM t") |
| 101 | + .await? |
| 102 | + .collect() |
| 103 | + .await?; |
| 104 | + |
| 105 | + assert_batches_eq!( |
| 106 | + &[ |
| 107 | + "+------------------------------------+", |
| 108 | + "| to_char(t.values,Utf8(\"%m-%d-%Y\")) |", |
| 109 | + "+------------------------------------+", |
| 110 | + "| 09-01-2020 |", |
| 111 | + "| 09-02-2020 |", |
| 112 | + "| 09-03-2020 |", |
| 113 | + "| 09-04-2020 |", |
| 114 | + "+------------------------------------+", |
| 115 | + ], |
| 116 | + &result |
| 117 | + ); |
| 118 | + |
| 119 | + // if you want to just use the default format cast to a string |
| 120 | + let result = ctx |
| 121 | + .sql("SELECT arrow_cast(values, 'Utf8') from t") |
| 122 | + .await? |
| 123 | + .collect() |
| 124 | + .await?; |
| 125 | + |
| 126 | + assert_batches_eq!( |
| 127 | + &[ |
| 128 | + "+------------+", |
| 129 | + "| t.values |", |
| 130 | + "+------------+", |
| 131 | + "| 2020-09-01 |", |
| 132 | + "| 2020-09-02 |", |
| 133 | + "| 2020-09-03 |", |
| 134 | + "| 2020-09-04 |", |
| 135 | + "+------------+", |
| 136 | + ], |
| 137 | + &result |
| 138 | + ); |
| 139 | + |
| 140 | + // use can use literals as well (note the use of timestamp here) |
| 141 | + let result = ctx |
| 142 | + .sql("SELECT to_char(arrow_cast(TIMESTAMP '2023-08-03 14:38:50Z', 'Timestamp(Second, None)'), '%d-%m-%Y %H:%M:%S')") |
| 143 | + .await? |
| 144 | + .collect() |
| 145 | + .await?; |
| 146 | + |
| 147 | + assert_batches_eq!( |
| 148 | + &[ |
| 149 | + "+-----------------------------------------------------------------+", |
| 150 | + "| to_char(Utf8(\"2023-08-03 14:38:50Z\"),Utf8(\"%d-%m-%Y %H:%M:%S\")) |", |
| 151 | + "+-----------------------------------------------------------------+", |
| 152 | + "| 03-08-2023 14:38:50 |", |
| 153 | + "+-----------------------------------------------------------------+", |
| 154 | + ], |
| 155 | + &result |
| 156 | + ); |
| 157 | + |
| 158 | + // durations are supported though the output format is limited to two formats |
| 159 | + // 'pretty' and 'ISO8601' |
| 160 | + let result = ctx |
| 161 | + .sql("SELECT to_char(arrow_cast(123456, 'Duration(Second)'), 'pretty')") |
| 162 | + .await? |
| 163 | + .collect() |
| 164 | + .await?; |
| 165 | + |
| 166 | + assert_batches_eq!( |
| 167 | + &[ |
| 168 | + "+---------------------------------------+", |
| 169 | + "| to_char(Int64(123456),Utf8(\"pretty\")) |", |
| 170 | + "+---------------------------------------+", |
| 171 | + "| 1 days 10 hours 17 mins 36 secs |", |
| 172 | + "+---------------------------------------+", |
| 173 | + ], |
| 174 | + &result |
| 175 | + ); |
| 176 | + |
| 177 | + // durations are supported though the output format is limited to two formats |
| 178 | + // 'pretty' and 'ISO8601' |
| 179 | + let result = ctx |
| 180 | + .sql("SELECT to_char(arrow_cast(123456, 'Duration(Second)'), 'iso8601')") |
| 181 | + .await? |
| 182 | + .collect() |
| 183 | + .await?; |
| 184 | + |
| 185 | + assert_batches_eq!( |
| 186 | + &[ |
| 187 | + "+----------------------------------------+", |
| 188 | + "| to_char(Int64(123456),Utf8(\"iso8601\")) |", |
| 189 | + "+----------------------------------------+", |
| 190 | + "| PT123456S |", |
| 191 | + "+----------------------------------------+", |
| 192 | + ], |
| 193 | + &result |
| 194 | + ); |
| 195 | + |
| 196 | + Ok(()) |
| 197 | +} |
0 commit comments