CALL load_aws_credentials();
--- duckdb >= v0.9.0, AWS and httpfs extension loaded on the fly
Copy code

with 

nested_json_list as (
    
    select '["a", "b", "c"]'::json as foo

)
    
select json(foo)::varchar[] from nested_json_list
Copy code

-- Here we make use of list_transform and 
-- the fact that DuckDB knows lambda expressions
SELECT list_transform(split('01:08:22', ':'), x -> CAST (x AS INTEGER));
Copy code

-- * EXCLUDE plus any additional expression does the trick
SELECT * EXCLUDE (valid_from),  -- valid_from used in computation
       year(valid_from) AS year -- A new expression
FROM prices
 -- Note that the new expression can be referred to in the WHERE clause
WHERE year BETWEEN 2019 AND 2020;
Copy code

-- bar(x, min, max[, width])
--- Draw a band whose width is proportional to (x - min) and equal to width characters when x = max. width defaults to 80.

with data as (select unnest(range(1,10)) as v order by random()) 

select bar(v,minv,maxv,30) from data, 
       (select min(v) as minv, max(v) as maxv from data);

-- ┌────────────────────────────────┐
-- │     bar(v, minv, maxv, 30)     │
-- │            varchar             │
-- ├────────────────────────────────┤
-- │ ██████████████████████▌        │
-- │ ███▊                           │
-- │ ██████████████████████████████ │
-- │                                │
-- │ ███████████████                │
-- │ ███████████▎                   │
-- │ ██████████████████▊            │
-- │ ██████████████████████████▎    │
-- │ ███████▌                       │
-- └────────────────────────────────┘
Copy code

-- duckdb has only weekday-name not number
-- create enum in the right order
create type Weekday as enum ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday');

-- cast the string to enum for sorting
select count(*), cast(dayname(CreationDate) as Weekday) as day
from posts where posttypeid = 1 and tags like '%>sql<%'
group by all
order by day asc;
-- doing the cast in order by causes a bug

-- ┌──────────────┬───────────┐
-- │ count_star() │    day    │
-- │    int64     │  weekday  │
-- ├──────────────┼───────────┤
-- │       103937 │ Monday    │
-- │       115575 │ Tuesday   │
-- │       119825 │ Wednesday │
-- │       119514 │ Thursday  │
-- │       103445 │ Friday    │
-- │        47139 │ Saturday  │
-- │        47390 │ Sunday    │
-- └──────────────┴───────────┘
Copy code

select round(2061899,-6); -- 2000000.0
Copy code

-- Using  Scaleway as an example, where first 75GB is free
-- https://www.scaleway.com/en/object-storage/
-- This also works for Google Storage, Digital Ocean Spaces, 
-- Wasabi and so on

-- set these like you do for AWS
SET s3_access_key_id='MY_KEY'
SET s3_secret_access_key='MY_SECRET'

-- set the endpoint and region to override the default of 
-- using AWS endpoints
SET s3_endpoint='s3.nl-ams.scw.cloud'
SET s3_region="nl-ams"

SELECT
  -- format a large number with helpful commas 
  -- for readability
  format('{:,}', count(*)) as "total rows in file" 
FROM "s3://<BUCKET_NAME>/<FILE_NAME>.zst.parquet"
Copy code