Example OmniSci.jl query

omnisci_getting_started.jl

               _
   _       _ _(_)_     |  Documentation: https://docs.julialang.org
  (_)     | (_) (_)    |
   _ _   _| |_  __ _   |  Type "?" for help, "]?" for Pkg help.
  | | | | | | |/ _` |  |
  | | |_| | | | (_| |  |  Version 1.1.0 (2019-01-21)
 _/ |\__'_|_|_|\__'_|  |  Official https://julialang.org/ release
|__/                   |

julia> using OmniSci, DataFrames

julia> conn = OmniSci.connect("localhost", 6274, "admin", "HyperInteractive", "omnisci")
Connected to localhost:6274

julia> query = 
       """
       select
       flight_year,
       flight_month,
       flight_dayofmonth,
       uniquecarrier,
       carrier_name,
       origin_city,
       count(*) as total_flights,
       avg(case when depdelay > 0 then 1 else 0 end) as pct_flights_delayed_depart
       from flights_2008_7m
       where carrier_name = 'US Airways' and origin_city = 'Philadelphia'
       group by 1,2,3,4,5,6
       """
"select\nflight_year,\nflight_month,\nflight_dayofmonth,\nuniquecarrier,\ncarrier_name,\norigin_city,\ncount(*) as total_flights,\navg(case when depdelay > 0 then 1 else 0 end) as pct_flights_delayed_depart\nfrom flights_2008_7m\nwhere carrier_name = 'US Airways' and origin_city = 'Philadelphia'\ngroup by 1,2,3,4,5,6\n"

julia> departure_delay_by_day = sql_execute(conn, query)
366×8 DataFrame
│ Row │ flight_year │ flight_month │ flight_dayofmonth │ uniquecarrier │ carrier_name │ origin_city  │ total_flights │ pct_flights_delayed_depart │
│     │ Int16⍰      │ Int16⍰       │ Int16⍰            │ String⍰       │ String⍰      │ String⍰      │ Int32⍰        │ Union{Missing, Float64}    │
├─────┼─────────────┼──────────────┼───────────────────┼───────────────┼──────────────┼──────────────┼───────────────┼────────────────────────────┤
│ 1   │ 2008        │ 10           │ 13                │ US            │ US Airways   │ Philadelphia │ 130           │ 0.384615                   │
│ 2   │ 2008        │ 4            │ 10                │ US            │ US Airways   │ Philadelphia │ 130           │ 0.538462                   │
│ 3   │ 2008        │ 10           │ 11                │ US            │ US Airways   │ Philadelphia │ 98            │ 0.397959                   │
│ 4   │ 2008        │ 3            │ 21                │ US            │ US Airways   │ Philadelphia │ 132           │ 0.44697                    │
│ 5   │ 2008        │ 2            │ 6                 │ US            │ US Airways   │ Philadelphia │ 123           │ 0.333333                   │
│ 6   │ 2008        │ 4            │ 11                │ US            │ US Airways   │ Philadelphia │ 130           │ 0.523077                   │
│ 7   │ 2008        │ 8            │ 3                 │ US            │ US Airways   │ Philadelphia │ 119           │ 0.428571                   │
│ 8   │ 2008        │ 11           │ 23                │ US            │ US Airways   │ Philadelphia │ 114           │ 0.315789                   │
⋮
│ 358 │ 2008        │ 1            │ 18                │ US            │ US Airways   │ Philadelphia │ 123           │ 0.520325                   │
│ 359 │ 2008        │ 1            │ 5                 │ US            │ US Airways   │ Philadelphia │ 94            │ 0.56383                    │
│ 360 │ 2008        │ 8            │ 11                │ US            │ US Airways   │ Philadelphia │ 130           │ 0.653846                   │
│ 361 │ 2008        │ 10           │ 19                │ US            │ US Airways   │ Philadelphia │ 119           │ 0.403361                   │
│ 362 │ 2008        │ 7            │ 25                │ US            │ US Airways   │ Philadelphia │ 129           │ 0.465116                   │
│ 363 │ 2008        │ 8            │ 6                 │ US            │ US Airways   │ Philadelphia │ 130           │ 0.484615                   │
│ 364 │ 2008        │ 6            │ 30                │ US            │ US Airways   │ Philadelphia │ 128           │ 0.351563                   │
│ 365 │ 2008        │ 12           │ 3                 │ US            │ US Airways   │ Philadelphia │ 120           │ 0.316667                   │
│ 366 │ 2008        │ 8            │ 18                │ US            │ US Airways   │ Philadelphia │ 130           │ 0.446154                   │
    
julia> eltypes(departure_delay_by_day)
8-element Array{Union,1}:
 Union{Missing, Int16}  
 Union{Missing, Int16}  
 Union{Missing, Int16}  
 Union{Missing, String} 
 Union{Missing, String} 
 Union{Missing, String} 
 Union{Missing, Int32}  
 Union{Missing, Float64}