randyzwitch

omnisql> copy baywheels_tripdata from 's3://baywheels-data/201801-fordgobike-tripdata.csv.zip';
Result
Loaded: 0 recs, Rejected: 94802 recs in 5.752000 secs

randyzwitch

--define table for 2017 schema
CREATE TABLE baywheels_tripdata(
duration_sec INTEGER,
start_time TIMESTAMP,
end_time TIMESTAMP,
start_station_id SMALLINT,
start_station_name TEXT ENCODING DICT,
start_station_latitude FLOAT,
start_station_longitude FLOAT,
end_station_id SMALLINT,

randyzwitch

#run in OmniSci before import to define table
create table customers_nopii(
	customerid TEXT ENCODING DICT,
	service_address TEXT ENCODING DICT,
	address_geom POLYGON
)

#export custom query to OmniSci
java -cp /opt/omnisci/bin/mapd-1.0-SNAPSHOT-jar-with-dependencies.jar:/path/mssql-jdbc-7.4.1.jre8.jar \
com.mapd.utility.SQLImporter \

randyzwitch

java -cp /opt/omnisci/bin/mapd-1.0-SNAPSHOT-jar-with-dependencies.jar:/path/mssql-jdbc-7.4.1.jre8.jar \
com.mapd.utility.SQLImporter \
-d com.microsoft.sqlserver.jdbc.SQLServerDriver \
-u admin -p HyperInteractive -db omnisci --port 6274 -t customers \
-su sqlserver_user -sp sqlserver_password \
-c "jdbc:sqlserver://localhost:1433;DatabaseName=prod" \
-ss "select * from dbo.customers"

randyzwitch

$ java -version
java version "1.8.0_181"
Java(TM) SE Runtime Environment (build 1.8.0_181-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.181-b13, mixed mode)

randyzwitch

[
  {
    "customStyles": {
      "colors": {
        "solid": [
          "#ea5545",
          "#f46a9b",
          "#ef9b20",
          "#ede15b",
          "#bdcf32",

randyzwitch

julia> geodf = sql_execute(conn, "select * from omnisci_states")
52×4 DataFrame. Omitted printing of 1 columns
│ Row │ id      │ abbr    │ name                 │
│     │ String⍰ │ String⍰ │ String⍰              │
├─────┼─────────┼─────────┼──────────────────────┤
│ 1   │ 01      │ AL      │ Alabama              │
│ 2   │ 02      │ AK      │ Alaska               │
│ 3   │ 04      │ AZ      │ Arizona              │
│ 4   │ 05      │ AR      │ Arkansas             │
│ 5   │ 11      │ DC      │ District of Columbia │

randyzwitch

               _
   _       _ _(_)_     |  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

randyzwitch

# run image interactively
docker run -it --runtime=nvidia --rm nvidia/cuda:10.0-devel-centos7 bash

# update image
yum update -y && yum install git sudo -y

# clone project and download/install dependencies
git clone https://github.com/omnisci/omniscidb.git
bash /omniscidb/scripts/mapd-deps-prebuilt.sh

randyzwitch

import requests
import pandas as pd

user = "user"
password = "password"

# authenticate to get user_id and session_id
authcall = requests.post("https://api.pelotoncycle.com/auth/login",
                          json = {"username_or_email": user, "password": password})