Skip to content

Instantly share code, notes, and snippets.

View randyzwitch's full-sized avatar

Randy Zwitch randyzwitch

251 followers · 3 following
View GitHub Profile
@randyzwitch
randyzwitch / extra_col.sql
Last active November 7, 2019 17:25
Baywheels extra column schema
--201906 and 201907 files have extra blank column
CREATE TABLE baywheels_tripdata_extracol(
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
randyzwitch / june_2019.sql
Created November 6, 2019 21:56
June 2019 file fails
omnisql> copy baywheels_tripdata from 's3://baywheels-data/201905-baywheels-tripdata.csv.zip';
Result
Loaded: 182163 recs, Rejected: 0 recs in 2.440000 secs
omnisql> copy baywheels_tripdata from 's3://baywheels-data/201906-baywheels-tripdata.csv.zip';
Result
Loader truncated due to reject count. Processed : 0 recs, Rejected: 191772 recs in 8.323000 secs
@randyzwitch
randyzwitch / alter_table.sql
Created November 6, 2019 21:46
ALTER TABLE ADD COLUMN in OmniSci
--add column bike_share_for_all_trip, which starts with Jan 2018 file
ALTER TABLE baywheels_tripdata ADD COLUMN bike_share_for_all_trip TEXT ENCODING DICT;
@randyzwitch
randyzwitch / 201801_loadingfailure.sh
Created November 6, 2019 21:29
Failure to load any records in OmniSci
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
randyzwitch / 2017_baywheels_omnisci.sql
Created November 6, 2019 21:20
2017 FordGoBike/Baywheels data in OmniSci
--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
randyzwitch / customers_nopii.sh
Last active September 25, 2019 13:03
Limited Select Statement SQL Server to OmnISci
#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
randyzwitch / sqlserver-to-omnisci.sh
Last active September 24, 2019 20:17
Example SQL Server to OmniSci SQLImporter code
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
randyzwitch / java-version.sh
Created September 24, 2019 19:54
Checking Java version
$ 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
randyzwitch / servers-colorbrewer-sequential.json
Created August 23, 2019 19:41
OmniSci Immerse servers.json with ColorBrewer sequential scales
[
{
"customStyles": {
"colors": {
"solid": [
"#ea5545",
"#f46a9b",
"#ef9b20",
"#ede15b",
"#bdcf32",
@randyzwitch
randyzwitch / omnisci_getting_started.jl
Last active July 26, 2019 20:17
Example OmniSci.jl query
_
_ _ _(_)_ | 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