afcotroneo

## connect to your omnisci server

ssh -i ~/.ssh/yourkey.pem [email protected]

afcotroneo

#Create Policy
CREATE POLICY ON COLUMN table.column TO <name> VALUES ('string', 123, ...);

#Drop Policy
DROP POLICY ON COLUMN table.column FROM <name>;

afcotroneo

CREATE TABLE df_snow_geo as (
  SELECT
    a.id,
    a.dt,
    a.element_,
    a.value_,
    b.latitude,
    b.longitude,
    b.elevation,
    b.NAME

afcotroneo

import gc

cols = ["id", "dt", "element_", "value_", "m_flag", "q_flag", "s_flag", "obs_time"]

for i in range(1900,2022):
    con = connect(user="l", password="!", host="",port=0, dbname="")
        
    url = "https://noaa-ghcn-pds.s3.amazonaws.com/csv/" + str(i) + ".csv"
    
    data = pd.read_csv(url,names=cols,parse_dates=["dt"],dtype={'id':'str','element_':'str','value_':'int32','m_flag':'str','q_flag':'str','s_flag':'str','obs_time':'object'},iterator=True ,chunksize=1000000)

afcotroneo

for chunk in np.array_split(df, df.shape[0]):
   dot_density_and_pushtoomnisci(chunk,'2000-01-01T00:12:00.000Z',50)

afcotroneo

seed = 10
s=RandomState(seed) if seed else RandomState(seed)

def gen_random_points_poly(poly, num_points):
   """
   Returns a list of N randomly generated points within a polygon.
   """
  
   min_x, min_y, max_x, max_y = poly.bounds
   points = []

afcotroneo

STATEFIPS = ["01","04","05","06","08","09","10","12","13","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31","32","33","34","35","36","37","38","39","40","41","42","44","45","46","47","48","49","50","51","53","54","55","56"]

col_names = ["white_pop", "black_pop","asian_pop", "amerindian_pop","hispanic_pop",  "other_race_pop", "two_or_more_races_pop", "total_pop", "state","county","tract","block_group"]

for st_fips in STATEFIPS:
# Get these fields of data for the year 2000
 r2000 = requests.get("https://api.census.gov/data/2000/dec/sf1?get=P004005,P004006,P004008,P004007,P004002,P004010,P004011,P004001&for=block%20group:*&in=state:{}&in=county:*&in=tract:*&key=30699f15ab4d04a1e0943715b539d256c9a3ee44".format(st_fips))

# Create a data frame with the results
 df = pd.DataFrame(columns=col_names, data=r2000.json()[1:])

afcotroneo

#FILE_SORT_ORDER_BY

COPY table_1 from ".../" WITH (FILE_SORT_ORDER_BY="date_modified");

afcotroneo

#Filtering

COPY table_1 from ".../" WITH (REGEX_PATH_FILTER=".*file_[4-5]");

returns file_4, file_5

afcotroneo

#Recursive Globbing

COPY table_1 FROM ".../subdir";

returns file_3, file_4, file_5

#Wildcard Globbing

COPY table_1 FROM ".../subdir/file*";