def create_movies():
engine = create_engine('sqlite:///data.sqlite')
create_table_from_csv(engine,
"sample_movies.csv",
table_name="movies",
fields=[("id", "integer"), ("title", "string"),
("genres", "string")],
create_id=False)
def create_ratings():
engine = create_engine('sqlite:///data.sqlite')
create_table_from_csv(engine,
"syntetic_ratings.csv",
table_name="ratings",
fields=[("user_id", "integer"),
("item_id", "integer"), ("rating", "float"),
("time", "integer")],
create_id=False)
#
# Create a tutorial directory and download the file:
# 1. Prepare SQL data in memory
logger = logging.getLogger("cubes")
logger.setLevel(logging.WARN)
FACT_TABLE = "irbd_balance"
engine = sqlalchemy.create_engine('sqlite:///:memory:')
tutorial.create_table_from_csv(engine,
"data/IBRD_Balance_Sheet__FY2010-t02.csv",
table_name=FACT_TABLE,
fields=[("category", "string"),
("subcategory", "string"),
("line_item", "string"),
("year", "integer"),
("amount", "integer")],
create_id=True)
# 2. Load model and get cube of our interest
model = cubes.load_model("models/model_02.json")
cube = model.cube("irbd_balance")
# 3. Create a browser
workspace = cubes.create_workspace("sql.star", model, engine=engine)
browser = workspace.browser(cube)
from cubes.tutorial.sql import create_table_from_csv
from sqlalchemy import create_engine
import sys
# Setting encoding to UTF-8
reload(sys)
sys.setdefaultencoding('utf8')
# FACT table name
FACT_TABLE = "restaurant_details"
print("preparing data...")
engine = create_engine('sqlite:///restaurant.sqlite')
# Creating fact table from restaurant_details.csv
create_table_from_csv(engine,
"./restaurant_details.csv",
table_name=FACT_TABLE,
fields=[("id", "integer"),
("number_of_reviews", "integer"),
("price_range", "float"),
("ratingValue", "float"), ("name", "string"),
("street", "string"), ("city", "string"),
("state", "string"), ("zipcode", "integer")])
print("restaurant.sqlite created")
import sqlalchemy
import cubes
import cubes.tutorial.sql as tutorial
# 1. Prepare SQL data in memory
engine = sqlalchemy.create_engine("sqlite:///:memory:")
tutorial.create_table_from_csv(
engine,
"data/IBRD_Balance_Sheet__FY2010.csv",
table_name="irbd_balance",
fields=[("category", "string"), ("line_item", "string"), ("year", "integer"), ("amount", "integer")],
create_id=True,
)
# 2. Create a model
model = cubes.Model()
# 3. Add dimensions to the model. Reason for having dimensions in a model is, that they
# might be shared by multiple cubes.
model.add_dimension(cubes.Dimension("category"))
model.add_dimension(cubes.Dimension("line_item"))
model.add_dimension(cubes.Dimension("year"))
# 3. Define a cube and specify already defined dimensions
cube = cubes.Cube(name="irbd_balance", model=model, dimensions=["category", "line_item", "year"], measures=["amount"])
# 4. Create a browser and get a cell representing the whole cube (all data)
# -*- coding: utf-8 -*-
# Data preparation
from __future__ import print_function
from sqlalchemy import create_engine
from cubes.tutorial.sql import create_table_from_csv
# 1. Prepare SQL data in memory
print("preparing data...")
engine = create_engine('sqlite:///personal_finance.sqlite')
create_table_from_csv(engine,
"personal_finance.csv",
table_name="personal_finance",
fields=[
("type", "string"),
("amount", "integer"),
("time", "integer")],
create_id=True
)
print("csv to sqlite, ok")
engine = create_engine('sqlite:///data.sqlite')
if sys.argv[2] != "0":
create_flag = True
else:
create_flag = False
create_table_from_csv(engine,
sys.argv[1],
table_name=FACT_TABLE,
fields=[
("EventID","integer"),
("EventType","string"),
("RegionType","string"),
("Region","string"),
("Fulltime","string"),
("Date","date"),
("Year","integer"),
("Month","integer"),
("Day","integer"),
("Time","date"),
("Latitude","float"),
("Longitude","float"),
("Depth","float"),
("Magnitude","float"),
("MagnitudeUnit","string")],
create_id=True,
create_table=create_flag
)
print("done. file data.sqlite created")
from sqlalchemy import create_engine
from cubes.tutorial.sql import create_table_from_csv
import sys
# 1. Prepare SQL data in memory
FACT_TABLE = "quake_events"
print("preparing data...")
engine = create_engine('sqlite:///data.sqlite')
if sys.argv[2] != "0":
create_flag = True
else:
create_flag = False
create_table_from_csv(engine,
sys.argv[1],
table_name=FACT_TABLE,
fields=[("EventID", "integer"), ("EventType", "string"),
("RegionType", "string"), ("Region", "string"),
("Fulltime", "string"), ("Date", "date"),
("Year", "integer"), ("Month", "integer"),
("Day", "integer"), ("Time", "date"),
("Latitude", "float"), ("Longitude", "float"),
("Depth", "float"), ("Magnitude", "float"),
("MagnitudeUnit", "string")],
create_id=True,
create_table=create_flag)
print("done. file data.sqlite created")
from cubes.tutorial.sql import create_table_from_csv
engine = create_engine('sqlite:///data.sqlite')
create_table_from_csv(engine,
"E_merge.csv",
table_name="restaurants",
fields=[("id", "integer"), ("name", "string"),
("city", "string"), ("zipcode", "string"),
("address", "string"), ("phone", "string"),
("category", "string"),
("category_loc", "string"),
("category_food", "string"), ("price", "float"),
("rating", "float"), ("review_count", "integer"),
("hours_sun_open", "string"),
("hours_sun_close", "string"),
("hours_mon_open", "string"),
("hours_mon_close", "string"),
("hours_tue_open", "string"),
("hours_tue_close", "string"),
("hours_wed_open", "string"),
("hours_wed_close", "string"),
("hours_thu_open", "string"),
("hours_thu_close", "string"),
("hours_fri_open", "string"),
("hours_fri_close", "string"),
("hours_sat_open", "string"),
("hours_sat_close", "string")],
create_id=False)
from cubes import Workspace
from cubes import PointCut
# Preparacion de la Data a utilizar en el ejemplo
from sqlalchemy import create_engine
from cubes.tutorial.sql import create_table_from_csv
# 1. Preparando la data SQL en memoria
FACT_TABLE = "fact_ventas"
print "[fact_ventas][INICIO] Preparacion de Datos..."
engine = create_engine('sqlite:///data.sqlite')
create_table_from_csv(engine,
"fact_ventas.csv",
table_name=FACT_TABLE,
fields=[("vendedor", "string"),
("cliente", "string"),
("indicador", "string"),
("valor", "integer"),
("fecha", "date"),
("tipo", "string")],
create_id=True
)
print "[fact_ventas][FIN] archivo data.sqlite creado"
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time : 2018/9/14 下午11:35
# @Author : maidou
# @Site :
# @File : test.py
# @Software: PyCharm
from cubes.tutorial.sql import create_table_from_csv
from sqlalchemy import create_engine
engine = create_engine('sqlite:///data.sqlite')
create_table_from_csv(engine,
"IBRD_Balance_Sheet__FY2010.csv",
table_name="ibrd_balance",
fields=[
("category", "string"),
("category_label", "string"),
("subcategory", "string"),
("subcategory_label", "string"),
("line_item", "string"),
("year", "integer"),
("amount", "integer")],
create_id=True
)
# driver, circuits, races, constructors, status, result
from sqlalchemy import create_engine
from cubes.tutorial.sql import create_table_from_csv
ENGINE = create_engine('sqlite:///data_sqlite/f1.sqlite')
TABLE_NAME = 'drivers'
PATH_TO_CSV = 'data_csv/drivers.csv'
create_table_from_csv(ENGINE,
PATH_TO_CSV,
table_name=TABLE_NAME,
fields=[('driver_id', 'integer'),
('driver_ref', 'string'), ('number', 'integer'),
('code', 'string'), ('forename', 'string'),
('surname', 'string'), ('dob', 'date'),
('nationality', 'string'), ('url', 'string')],
create_id=True)
'''TABLE_NAME = 'qualifying'
PATH_TO_CSV = 'data_csv/qualifying.csv'
create_table_from_csv(ENGINE,
PATH_TO_CSV,
table_name=TABLE_NAME,
fields=[
('qualify_id', 'integer'),
('race_id', 'integer'),
('driver_id', 'integer'),
('constructor_id', 'integer'),
('number', 'integer'),
from sqlalchemy import create_engine
from cubes.tutorial.sql import create_table_from_csv
engine = create_engine('sqlite:///world_cup.sqlite')
print("...wait (about 3 minutes)")
create_table_from_csv(engine,
"WC18.csv",
table_name="players",
fields=[("name", "string"), ("nat_team", "string"),
("club", "string"), ("league", "string"),
("pos", "string"), ("role", "string"),
("age_gr", "string"), ("age_ex", "string"),
("matches", "integer"), ("goals", "integer"),
("assists", "integer"), ("ycards", "integer"),
("rcards", "integer"), ("minutes", "integer"),
("n-caps", "integer"), ("n-goals", "integer")],
create_id=True)
create_table_from_csv(engine,
"data.csv",
table_name=FACT_TABLE,
fields=[
("state_code", "integer"),
("county_code", "integer"),
("site_num", "integer"),
("parameter_code", "integer"),
("poc", "integer"),
("datum", "string"),
("parameter_name", "string"),
("sample_duration", "string"),
("pollutant_standard", "string"),
("metric_used", "string"),
("method_name", "string"),
("year", "integer"),
("measure_unit", "string"),
("event_type", "string"),
("observation_count", "integer"),
("observation_percent1", "integer"),
("valid_day_count", "integer"),
("required_day_count", "integer"),
("exceptional_data_count", "integer"),
("null_data_count", "integer"),
("primary_exceedence_count", "integer"),
("secondary_exceedence_count", "integer"),
("certification_indicator", "string"),
("num_obs_below_mdl", "integer"),
("arithmetic_mean", "float"),
("arithmetic_stdev", "float"),
("max1", "float"),
("max1_datetime", "text"),
("max2", "float"),
("max2_datetime", "text"),
("max3", "float"),
("max3_datetime", "text"),
("max4", "float"),
("max4_datetime", "text"),
("max1_non_overlapping", "integer"),
("max1_non_overlapping_datetime", "text"),
("max2_non_overlapping", "integer"),
("max2_non_overlapping_datetime", "text"),
("percentile_99", "float"),
("percentile_98", "float"),
("percentile_95", "float"),
("percentile_90", "float"),
("percentile_75", "float"),
("percentile_50", "float"),
("percentile_10", "float"),
("local_site", "string"),
("state", "string"),
("county_name", "string"),
("city", "string"),
("cbsa_name", "string"),
("date_of_last_change", "text"),
("subarea", "string")],
create_id=True
)
# Create a tutorial directory and download the file:
# 1. Prepare SQL data in memory
logger = logging.getLogger("cubes")
logger.setLevel(logging.WARN)
FACT_TABLE = "irbd_balance"
engine = sqlalchemy.create_engine('sqlite:///:memory:')
tutorial.create_table_from_csv(engine,
"data/IBRD_Balance_Sheet__FY2010-t02.csv",
table_name=FACT_TABLE,
fields=[
("category", "string"),
("subcategory", "string"),
("line_item", "string"),
("year", "integer"),
("amount", "integer")],
create_id=True
)
# 2. Load model and get cube of our interest
model = cubes.load_model("models/model_02.json")
cube = model.cube("irbd_balance")
# 3. Create a browser
workspace = cubes.create_workspace("sql.star", model, engine=engine)
browser = workspace.browser(cube)
logger = logging.getLogger("cubes")
logger.setLevel(logging.WARN)
FACT_TABLE = "ft_irbd_balance"
FACT_VIEW = "vft_irbd_balance"
print "loading data..."
engine = sqlalchemy.create_engine('sqlite:///data.sqlite')
tutorial.create_table_from_csv(engine,
"data.csv",
table_name=FACT_TABLE,
fields=[
("category", "string"),
("category_label", "string"),
("subcategory", "string"),
("subcategory_label", "string"),
("line_item", "string"),
("year", "integer"),
("amount", "integer")],
create_id=True
)
model = cubes.load_model("model.json")
cube = model.cube("irbd_balance")
cube.fact = FACT_TABLE
# 2. Create the view (required for the default backend)
print "creating view '%s'..." % FACT_VIEW
logger = logging.getLogger("cubes")
logger.setLevel(logging.WARN)
FACT_TABLE = "ft_iris"
FACT_VIEW = "vft_iris"
print "loading data..."
engine = sqlalchemy.create_engine("sqlite:///data.sqlite")
tutorial.create_table_from_csv(
engine,
"iris.csv",
table_name=FACT_TABLE,
fields=[
("iris_id", "integer"),
("sepal_length", "string"),
("sepal_width", "string"),
("petal_length", "string"),
("petal_width", "string"),
("species", "string"),
],
create_id=True,
)
model = cubes.load_model("model.json")
cube = model.cube("iris")
cube.fact = FACT_TABLE
# 2. Create the view (required for the default backend)
print "creating view '%s'..." % FACT_VIEW