def steps(params):
db_connector = Connector.fetch("clickhouse-database",
open("../conns.yaml"))
dtype = {
"cve_ent": "String",
"cve_mun": "String",
"cve_mun_full": "String",
"ent_name": "String",
"mun_name": "String",
"ent_id": "UInt8",
"mun_id": "UInt16",
"nation_name": "String",
"nation_id": "String",
"self_city_id": "UInt8",
"self_city_name": "String"
}
download_step = DownloadStep(
connector=["geo-data", "geo-selfrepresented"],
connector_path="conns.yaml")
transform_step = TransformStep()
load_step = LoadStep("dim_shared_geography_self_city",
db_connector,
if_exists="drop",
dtype=dtype,
pk=["ent_id", "mun_id", "self_city_id"])
return [download_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch('clickhouse-database',
open('../conns.yaml'))
dtype = {
'mun_id': 'UInt16',
'sex': 'UInt8',
'year': 'UInt16',
'age': 'UInt8',
'population': 'UInt32'
}
download_step = DownloadStep(
connector=['population-data-1', 'population-data-2'],
connector_path="conns.yaml")
transform_step = TransformStep()
load_step = LoadStep('legacy_population_projection',
db_connector,
if_exists='drop',
pk=['mun_id'],
dtype=dtype)
return [download_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch('clickhouse-database', open('../conns.yaml'))
dtype = {
'chapter': 'UInt8',
'chapter_es': 'String',
'chapter_en': 'String',
'chapter_es_short': 'String',
'chapter_en_short': 'String',
'hs2_id': 'UInt16',
'hs2_es': 'String',
'hs2_en': 'String',
'hs2_es_short': 'String',
'hs2_en_short': 'String',
'hs4_id': 'UInt32',
'hs4_es': 'String',
'hs4_en': 'String',
'hs4_es_short': 'String',
'hs4_en_short': 'String'
}
download_step = DownloadStep(
connector='hs6-2012',
connector_path='conns.yaml'
)
transform_step = TransformStep()
load_step = LoadStep('dim_shared_hs12_4digit', db_connector, if_exists='drop', pk=['hs4_id', 'hs2_id', 'chapter'], dtype=dtype)
return [download_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch("clickhouse-database",
open("../conns.yaml"))
dtype = {
"ent_id": "UInt8",
"population": "UInt64",
"illiterate_population": "Float32",
"population_6_14_school": "Float32",
"population_15_incomplete_school": "Float32",
"no_health_services": "Float32",
"dirt_floor": "Float32",
"no_toilet": "Float32",
"no_water_supply_network": "Float32",
"no_sewer_system": "Float32",
"no_electrical_energy": "Float32",
"no_washing_machine": "Float32",
"no_fridge": "Float32",
"social_lag_index": "Float32",
"social_lag_degree": "UInt8",
"year": "UInt16"
}
download_step = DownloadStep(connector="social-lag-data",
connector_path="conns.yaml")
transform_step = TransformStep()
load_step = LoadStep("coneval_social_lag_ent",
db_connector,
if_exists="append",
pk=["ent_id", "year"],
dtype=dtype,
nullable_list=["population"])
return [download_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch("clickhouse-database",
open("../conns.yaml"))
dtypes = {
"bimester_id": "UInt32",
"number_companies": "UInt64",
"harmonization_payment": "Float64",
"payment_contribution_with_credit": "Float64",
"payment_contribution_without_credit": "Float64",
"nat_id": "String"
}
download_step = DownloadStep(connector="payment-entity-credits",
connector_path="conns.yaml",
force=True)
transform_step = TransformStep()
load_step = LoadStep("infonavit_payment_entity_credits",
db_connector,
if_exists="drop",
pk=["bimester_id"],
dtype=dtypes)
return [download_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch("clickhouse-database",
open("../conns.yaml"))
dtypes = {
"month_id": "UInt32",
"beneficiaries_count": "UInt32",
"contribution_amount_viv92": "Float64",
"subaccount_balance_regime97": "Float64",
"participation_amount_viv92": "Float64",
"participation_amount_viv97": "Float64",
"contribution_amount_viv92_imss": "Float64",
"subaccount_balance_regime97_imss": "Float64",
"participation_amount_viv92_imss": "Float64",
"participation_amount_viv97_imss": "Float64",
"nat_id": "String"
}
download_step = DownloadStep(connector="housing-participation-credits",
connector_path="conns.yaml",
force=True)
transform_step = TransformStep()
load_step = LoadStep("infonavit_housing_participation_credits",
db_connector,
if_exists="drop",
pk=["month_id"],
dtype=dtypes)
return [download_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch('clickhouse-database',
open('../conns.yaml'))
dtype = {
'campus_id': 'UInt64',
'campus_name': 'String',
'institution_id': 'UInt32',
'institution_name': 'String'
}
download_step = DownloadStep(connector='dim-institution',
connector_path='conns.yaml',
force=True)
read_step = ReadStep()
transform_step = TransformStep()
load_step = LoadStep('dim_anuies_institutions',
db_connector,
if_exists='drop',
pk=['campus_id', 'institution_id'],
dtype=dtype)
return [download_step, read_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch('clickhouse-database',
open("../conns.yaml"))
dtype = {
"ent_id": "UInt8",
"ent_name": "String",
"zm_id": "UInt32",
"zm_name": "String",
"mun_id": "UInt16",
"mun_name": "String",
"loc_id": "UInt32",
"loc_name": "String",
"nation_name": "String",
"nation_id": "String"
}
download_step = DownloadStep(connector=["geo-zm", "geo-data-2"],
connector_path="conns.yaml")
transform_step = TransformStep()
load_step = LoadStep("dim_shared_geography_zm_loc",
db_connector,
if_exists="drop",
dtype=dtype,
pk=["zm_id", "mun_id", "loc_id"])
return [download_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch("clickhouse-database", open("../conns.yaml"))
dtype = {
'ent_id': 'UInt8',
'year': 'UInt16',
'country': 'String',
'investment_type': 'String',
'count': 'UInt16',
'value_c': 'Float32'
}
download_step = DownloadStep(
connector="fdi-data",
connector_path="conns.yaml",
force=True
)
transform_step = TransformStep()
load_step = LoadStep(
'fdi_4', db_connector, if_exists="drop",
pk=['ent_id', 'country'], dtype=dtype
)
return [download_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch("clickhouse-database",
open("../conns.yaml"))
download_step = DownloadStep(connector=params.get('db-source'),
connector_path="conns.yaml",
force=True)
read_step = ReadStep()
load_step = LoadStep(params.get('table'),
db_connector,
if_exists=params.get('if_exists'),
pk=[params.get('pk')],
dtype=params.get('dtype'))
if params.get('db-source') == 'fdi-data-additional-2':
transform_step = TransformInvestmentStep()
elif params.get('db-source') == 'fdi-data-additional':
if int(params.get('sheet_name')) in range(4, 7):
transform_step = TransformCountryStep()
else:
transform_step = TransformStateStep()
else:
if int(params.get('sheet_name')) in range(1, 4):
transform_step = TransformYearStep()
elif int(params.get('sheet_name')) in range(4, 7):
transform_step = TransformYearQuarterStep()
else:
transform_step = TransformYearInvestmentStep()
return [download_step, read_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch("clickhouse-database",
open("../conns.yaml"))
dtype = {
"mun_id": "UInt16",
"year": "UInt16",
"sex": "UInt8",
"national_industry_id": "String",
"employed_workers": "UInt32",
"personal_dependent_on_the_social_reason": "UInt32",
"paid_staff": "UInt32",
"production_personnel_sales_and_service": "UInt32",
"administrative_accounting_and_managerial_staff": "UInt16",
"owners_family_and_other_unpaid_workers": "UInt16",
"staff_not_dependent_on_the_social_reason": "UInt16",
"personnel_provided_by_another_company_name": "UInt16",
"staff_for_fees_or_commissions_without_basic_salary": "UInt16"
}
download_step = DownloadStep(connector="dataset",
connector_path="conns.yaml")
transform_step = TransformStep()
load_step = LoadStep(
"inegi_economic_census_sex",
db_connector,
if_exists="append",
pk=["mun_id", "year"],
dtype=dtype,
)
return [download_step, transform_step, load_step]
def steps(params, **kwargs):
# Use of connectors specified in the conns.yaml file
db_connector = Connector.fetch("clickhouse-database",
open("../conns.yaml"))
dtype = {
"affected_legal_good_id": "UInt8",
"crime_type_id": "UInt16",
"crime_subtype_id": "UInt32",
"affected_legal_good_es": "String",
"crime_type_es": "String",
"crime_subtype_es": "String",
"affected_legal_good_en": "String",
"crime_type_en": "String",
"crime_subtype_en": "String"
}
# Definition of each step
download_step = DownloadStep(connector="dim-crime",
connector_path="conns.yaml")
read_step = ReadStep()
clean_step = CleanStep()
load_step = LoadStep(
"dim_shared_crimes_subtype",
db_connector,
if_exists="drop",
pk=["affected_legal_good_id", "crime_type_id", "crime_subtype_id"],
dtype=dtype)
return [download_step, read_step, clean_step, load_step]
def steps(params):
db_connector = Connector.fetch('clickhouse-database',
open('../conns.yaml'))
dtype = {
'sector_id': 'String',
'quarter_id': 'UInt16',
'value': 'UInt32'
}
download_step = DownloadStep(connector='gdp-data',
connector_path='conns.yaml',
force=True)
read_step = ReadStep()
clean_step = CleanStep()
load_step = LoadStep('inegi_gdp',
db_connector,
if_exists='drop',
pk=['quarter_id', 'sector_id'],
dtype=dtype)
return [download_step, read_step, clean_step, load_step]
def steps(params):
db_connector = Connector.fetch("clickhouse-database",
open("../conns.yaml"))
dtype = {
"country_id": "String",
"year": "UInt16",
"type_id": "UInt8",
"status_id": "UInt8",
"national": "UInt8",
"value": "Float32"
}
download_step = DownloadStep(
connector=["patents-data", "country-list"],
connector_path="conns.yaml",
)
transform_step = TransformStep()
load_step = LoadStep("impi_country_level",
db_connector,
if_exists="drop",
pk=["country_id"],
dtype=dtype)
return [download_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch('clickhouse-database',
open('../conns.yaml'))
dtype = {
'product_level': 'UInt8',
params.get('name') + '_id': params.get('type'),
'hs2_id': 'UInt16',
'hs4_id': 'UInt32',
'hs6_id': 'UInt32',
'flow_id': 'UInt8',
'partner_country': 'String',
'value': 'UInt64',
'month_id': 'UInt32',
'year': 'UInt16',
'url': 'String'
}
read_step = ReadStep()
download_step = DownloadStep(connector='foreign-trade',
connector_path='conns.yaml')
transform_step = TransformStep()
load_step = LoadStep(params.get('table') + params.get('name'),
db_connector,
if_exists='append',
pk=[
params.get('name') + '_id', 'partner_country',
'month_id', 'year', 'hs2_id', 'hs4_id',
'hs6_id', 'product_level'
],
dtype=dtype)
return [download_step, read_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch('clickhouse-database',
open('../conns.yaml'))
dtype = {
'iso3': 'String',
'trade_flow_id': 'UInt8',
'month_id': 'UInt32',
'trade_value': 'UInt64'
}
download_step = DownloadStep(
connector=['exports-destiny-monthly', 'imports-origin-monthly'],
connector_path='conns.yaml',
force=True)
read_step = ReadStep()
transform_step = TransformStep(connector=db_connector)
load_step = LoadStep('banxico_trade_flow',
db_connector,
dtype=dtype,
if_exists='drop',
pk=['iso3'])
return [download_step, read_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch('clickhouse-database', open('../conns.yaml'))
dtype={
"section_id": "UInt8",
"section": "String",
"section_en": "String",
"group_id": "UInt8",
"group": "String",
"group_en": "String",
"sub_group_id": "UInt16",
"sub_group": "String",
"sub_group_en": "String"
}
dl_step = DownloadStep(
connector="dim-enigh-expense-items",
connector_path="conns.yaml",
force=True
)
read_step = ReadStep()
load_step = LoadStep(
'dim_expense_items', db_connector, if_exists='drop', dtype=dtype,
pk=['sub_group_id']
)
return [dl_step, read_step, load_step]
def steps(params):
db_connector = Connector.fetch("clickhouse-database", open("../../conns.yaml"))
dtype = {
"age": "UInt8",
"marital_status": "UInt8",
"occupation": "UInt8",
"academic_degree": "UInt8",
"social_security": "UInt8",
"medical_center": "UInt8",
"year_decease": "UInt16",
"cie10": "String",
"year_of_register": "UInt16",
"mun_residence_id": "UInt16",
"mun_happening_id": "UInt16",
"count": "UInt8"
}
download_step = DownloadStep(
connector=["pregnancy-mortality-data", "dim-pregnancy-mortality"],
connector_path="conns.yaml",
force=True
)
transform_step = TransformStep()
load_step = LoadStep(
"dgis_pregnancy_mortality", db_connector, if_exists="drop", pk=["mun_residence_id", "year_decease"], dtype=dtype
)
return [download_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch('clickhouse-database',
open('../conns.yaml'))
dtype = {
"nat_id": "String",
"ent_id": "UInt8",
"year": "UInt16",
"decile": "String",
"income_source": "UInt8",
"value": "UInt32"
}
dl_step = DownloadStep(connector="enigh-income-expenses",
connector_path="conns.yaml",
force=True)
read_step = ReadStep()
transform_step = TransformStep()
load_step = LoadStep('inegi_enigh_income_source',
db_connector,
if_exists='append',
dtype=dtype,
pk=['ent_id'])
return [dl_step, read_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch("clickhouse-database",
open("../../conns.yaml"))
dtype = {
"age": "UInt8",
"sex_id": "UInt8",
"social_security": "UInt8",
"cie10": "String",
"date_id": "UInt32",
"mun_id": "UInt16",
"attention_time": "UInt16",
"count": "UInt16",
"over_time": "UInt8"
}
download_step = DownloadStep(connector="emergency-data",
connector_path="conns.yaml")
transform_step = TransformStep()
load_step = LoadStep("dgis_emergency",
db_connector,
if_exists="append",
pk=["sex_id", "mun_id"],
dtype=dtype,
nullable_list=["date_id", "social_security"])
return [download_step, transform_step, load_step]
def steps(params, **kwargs):
db_connector = Connector.fetch('clickhouse-database',
open('../conns.yaml'))
dtypes = {
'sector_id': 'String',
'subsector_id': 'String',
'industry_group_id': 'String',
'naics_industry_id': 'String',
'national_industry_id': 'String'
}
download_step = DownloadStep(connector='naics-scian-codes',
connector_path="conns.yaml")
read_step = ReadStep(connector=db_connector)
load_step = LoadStep('dim_shared_industry_economic_census',
db_connector,
dtype=dtypes,
if_exists='drop',
pk=[
'sector_id', 'subsector_id',
'industry_group_id', 'naics_industry_id',
'national_industry_id'
])
return [download_step, read_step, load_step]
def steps(params):
db_connector = Connector.fetch("clickhouse-database", open("../conns.yaml"))
dtype = {
"job_id": "UInt8",
"national_job": "UInt8",
"pay_mode": "UInt8",
"contract": "UInt8",
"contract_type": "UInt8",
"worked_hours": "Float32",
"sinco_id": "UInt16",
"scian_id": "String",
"business_type": "UInt8",
"business_size": "UInt8",
"eco_stratum": "UInt8",
"mun_id": "UInt16",
"population": "UInt16",
"sex": "UInt8",
"age": "UInt8",
"year": "UInt16"
}
download_step = DownloadStep(
connector=["enigh-job", "enigh-housing", "enigh-population", "enigh-jobs-expenses"],
connector_path="conns.yaml"
)
transform_step = TransformStep()
load_step = LoadStep(
"inegi_enigh_jobs", db_connector, if_exists="append", pk=["mun_id", "sex"], dtype=dtype,
nullable_list=["pay_mode", "contract", "contract_type", "business_type"]
)
return [download_step, transform_step, load_step]
def steps(params, **kwargs):
db_connector = Connector.fetch("clickhouse-database", open("../conns.yaml"))
dtypes = {
"mun_id": "UInt16",
"national_industry_id": "String",
"year": "UInt16"
}
download_step = DownloadStep(
connector="dataset",
connector_path="conns.yaml"
)
# Definition of each step
transform_step = MultiStep()
load_step = LoadStep(
"inegi_economic_census", db_connector, dtype=dtypes, if_exists="drop",
pk=["national_industry_id", "mun_id", "year"],
nullable_list=["m000a", "p000c", "a800a", "q000d", "p000a", "p000b", "p030c", "a511a", "m050a", "j203a",
"j300a", "j400a", "j500a", "j600a", "k010a", "k020a", "k030a", "k311a", "k041a", "k610a",
"k620a", "k060a", "k070a", "k810a", "k910a", "k950a", "k096a", "k976a", "m010a", "m030a",
"m090a", "p100a", "p100b", "p030a", "p030b", "q010a", "q020a", "q030a", "q400a", "q900a"]
)
return [download_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch('clickhouse-database',
open('../conns.yaml'))
dtype = {
"system_id": "UInt8",
"system_en": "String",
"system_es": "String",
"subsystem_id": "UInt8",
"subsystem_en": "String",
"subsystem_es": "String",
"product_id": "UInt16",
"product_en": "String",
"product_es": "String",
}
download_step = DownloadStep(connector='product-dim',
connector_path='conns.yaml')
transform_step = TransformStep()
load_step = LoadStep("dim_sniim_products",
db_connector,
dtype=dtype,
if_exists="drop",
pk=["product_id"])
return [download_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch('clickhouse-database', open('../conns.yaml'))
dtype = {
'ent_id': 'UInt8',
'mun_id': 'UInt16',
'sex': 'UInt8',
'level': 'String',
'person_type': 'UInt8',
'age_range': 'UInt8',
'count': 'UInt32'
}
download_step = DownloadStep(
connector=['wellness-ent-total', 'wellness-mun-total'],
connector_path='conns.yaml',
force=True
)
read_step = ReadStep()
transform_step = TransformStep()
load_step = LoadStep(
'wellness_credits', db_connector, dtype=dtype, if_exists='drop',
pk=['ent_id']
)
return [download_step, read_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch("clickhouse-database",
open('../conns.yaml'))
dtypes = {
'month_id': 'UInt32',
'trade_flow_id': 'UInt8',
'hs2_id': 'UInt16',
'value': 'UInt64'
}
download_step = DownloadStep(connector='product',
connector_path='conns.yaml',
force=True)
read_step = ReadStep()
xform_step = XformStep(connector=db_connector)
ld_step = LoadStep('inegi_foreign_trade_product',
db_connector,
if_exists="drop",
pk=['month_id', 'hs2_id', 'trade_flow_id'],
dtype=dtypes)
return [download_step, read_step, xform_step, ld_step]
def steps(params):
db_connector = Connector.fetch("clickhouse-database",
open("../conns.yaml"))
dtypes = {
"income_id": "UInt8",
"month_id": "UInt32",
"credits_number": "UInt32",
"check_amount": "Float64",
"infonavit_credit_amount": "Float64",
"subsidy_number": "UInt16",
"subsidy_amount": "Float64",
"nat_id": "String"
}
download_step = DownloadStep(connector="income-level-credits",
connector_path="conns.yaml",
force=True)
transform_step = TransformStep()
load_step = LoadStep("infonavit_income_credits",
db_connector,
if_exists="drop",
pk=["income_id"],
dtype=dtypes)
return [download_step, transform_step, load_step]
def steps(params):
db_connector = Connector.fetch("clickhouse-database",
open("../conns.yaml"))
dtype = {
"mun_id": "UInt16",
"clues_id": "String",
"codigo_postal": "UInt32",
"institution_id": "String",
"type_id": "UInt8",
"estrato_id": "UInt8",
"total_consultorios": "Float32",
"total_camas": "Float32",
"count": "UInt16",
"latitud": "String",
"longitud": "String",
"publication_time": "String"
}
download_step = DownloadStep(connector="establishments-data",
connector_path="conns.yaml",
force=True)
transform_step = TransformStep()
load_step = LoadStep("health_establishments",
db_connector,
if_exists="drop",
pk=["mun_id"],
dtype=dtype,
nullable_list=["codigo_postal"])
return [download_step, transform_step, load_step]
def steps(params, **kwargs):
# Use of connectors specified in the conns.yaml file
db_connector = Connector.fetch('clickhouse-database',
open('../conns.yaml'))
dtype = {
'chapter_id': 'String',
'category_es': 'String',
'category_en': 'String',
'cie10_3digit': 'String',
'cie10_3digit_es': 'String',
'cie10_3digit_en': 'String',
'cie10_4digit': 'String',
'cie10_4digit_es': 'String',
'cie10_4digit_en': 'String'
}
# Definition of each step
download_step = DownloadStep(connector="cie10",
connector_path="conns.yaml")
read_step = ReadStep()
clean_step = CleanStep()
load_step = LoadStep('dim_shared_cie10',
db_connector,
if_exists='drop',
pk=['chapter_id', 'cie10_3digit', 'cie10_4digit'],
dtype=dtype)
return [download_step, read_step, clean_step, load_step]
def steps(params):
db_connector = Connector.fetch('clickhouse-database', open('../conns.yaml'))
dtype = {
'operation_type': 'UInt8',
'month_id': 'UInt32',
'remittance_amount': 'Float64',
'remittance_quantity': 'Float64'
}
download_step = DownloadStep(
connector='income-remittances-monthly',
connector_path='conns.yaml',
force=True
)
read_step = ReadStep()
transform_step = TransformStep()
load_step = LoadStep(
'banxico_income_remittances', db_connector, dtype=dtype, if_exists='drop',
pk=['operation_type', 'month_id']
)
return [download_step, read_step, transform_step, load_step]