- Создал(а) Anastasiya Galkovskaya, редактировал(а) Эдуард Столяр июл. 28, 2023
Вы просматриваете старую версию данной страницы. Смотрите текущую версию.
Сравнить с текущим просмотр истории страницы
« Предыдущий Версия 22 Текущий »
В настоящем разделе приведены примеры настроек для различных СУБД, поддерживаемых SQL Backend.
ClickHouse
Пример настройки
curl --location --request PUT 'http://{ADDRESS}:{PORT}/viqube/sqlbackend' \
--header 'X-API-VERSION: 3.8' \
--header 'Content-Type: application/json' \
--data-raw '{
"enabled": true,
"jdbcOptions": {
"connection": "jdbc:clickhouse://{ADDRESS}:{PORT}/{DB_NAME}?user={USER};password={PASSWORD}",
"socket_timeout": 300000,
"isResetUpdateTime": false
},
"sqlMapping": {
"*dim_Aggregate_function": {
"columns": {
"*attr_Data_nachala_raboty_v_kom": {
"DATE": "*attr_Data_nachala_raboty_v_kom",
"DAY_NAME": "CASE DAYOFWEEK( \"dim_Aggregate_function\".\"*attr_Data_nachala_raboty_v_kom\") WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( \"dim_Aggregate_function\".\"*attr_Data_nachala_raboty_v_kom\")",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( \"dim_Aggregate_function\".\"*attr_Data_nachala_raboty_v_kom\")",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( \"dim_Aggregate_function\".\"*attr_Data_nachala_raboty_v_kom\")",
"HALFYEAR": "CASE QUARTER( \"dim_Aggregate_function\".\"*attr_Data_nachala_raboty_v_kom\") WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( \"dim_Aggregate_function\".\"*attr_Data_nachala_raboty_v_kom\")",
"MONTH_NAME": "CASE MONTH( \"dim_Aggregate_function\".\"*attr_Data_nachala_raboty_v_kom\") WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( \"dim_Aggregate_function\".\"*attr_Data_nachala_raboty_v_kom\")",
"WEEK": "WEEK( \"dim_Aggregate_function\".\"*attr_Data_nachala_raboty_v_kom\")",
"YEAR": "YEAR(\"dim_Aggregate_function\".\"*attr_Data_nachala_raboty_v_kom\")",
"YEAR_FOR_WEEK": "toISOYear( \"dim_Aggregate_function\".\"*attr_Data_nachala_raboty_v_kom\")"
},
"*attr_Data_poslednej_attestacii": {
"DATE": "*attr_Data_poslednej_attestacii",
"DAY_NAME": "CASE DAYOFWEEK( \"dim_Aggregate_function\".\"*attr_Data_poslednej_attestacii\") WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( \"dim_Aggregate_function\".\"*attr_Data_poslednej_attestacii\")",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( \"dim_Aggregate_function\".\"*attr_Data_poslednej_attestacii\")",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( \"dim_Aggregate_function\".\"*attr_Data_poslednej_attestacii\")",
"HALFYEAR": "CASE QUARTER( \"dim_Aggregate_function\".\"*attr_Data_poslednej_attestacii\") WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( \"dim_Aggregate_function\".\"*attr_Data_poslednej_attestacii\")",
"MONTH_NAME": "CASE MONTH( \"dim_Aggregate_function\".\"*attr_Data_poslednej_attestacii\") WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( \"dim_Aggregate_function\".\"*attr_Data_poslednej_attestacii\")",
"WEEK": "WEEK( \"dim_Aggregate_function\".\"*attr_Data_poslednej_attestacii\")",
"YEAR": "YEAR(\"dim_Aggregate_function\".\"*attr_Data_poslednej_attestacii\")",
"YEAR_FOR_WEEK": "toISOYear( \"dim_Aggregate_function\".\"*attr_Data_poslednej_attestacii\")"
},
"*attr_Data_rozhdeniya": {
"DATE": "*attr_Data_rozhdeniya",
"DAY_NAME": "CASE DAYOFWEEK( \"dim_Aggregate_function\".\"*attr_Data_rozhdeniya\") WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( \"dim_Aggregate_function\".\"*attr_Data_rozhdeniya\")",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( \"dim_Aggregate_function\".\"*attr_Data_rozhdeniya\")",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( \"dim_Aggregate_function\".\"*attr_Data_rozhdeniya\")",
"HALFYEAR": "CASE QUARTER( \"dim_Aggregate_function\".\"*attr_Data_rozhdeniya\") WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( \"dim_Aggregate_function\".\"*attr_Data_rozhdeniya\")",
"MONTH_NAME": "CASE MONTH( \"dim_Aggregate_function\".\"*attr_Data_rozhdeniya\") WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( \"dim_Aggregate_function\".\"*attr_Data_rozhdeniya\")",
"WEEK": "WEEK( \"dim_Aggregate_function\".\"*attr_Data_rozhdeniya\")",
"YEAR": "YEAR( \"dim_Aggregate_function\".\"*attr_Data_rozhdeniya\")",
"YEAR_FOR_WEEK": "toISOYear( \"dim_Aggregate_function\".\"*attr_Data_rozhdeniya\")"
},
"*attr_Data_vydachi": {
"DATE": "*attr_Data_vydachi",
"DAY_NAME": "CASE DAYOFWEEK( \"dim_Aggregate_function\".\"*attr_Data_vydachi\") WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( \"dim_Aggregate_function\".\"*attr_Data_vydachi\")",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( \"dim_Aggregate_function\".\"*attr_Data_vydachi\")",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( \"dim_Aggregate_function\".\"*attr_Data_vydachi\")",
"HALFYEAR": "CASE QUARTER( \"dim_Aggregate_function\".\"*attr_Data_vydachi\") WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( \"dim_Aggregate_function\".\"*attr_Data_vydachi\")",
"MONTH_NAME": "CASE MONTH( \"dim_Aggregate_function\".\"*attr_Data_vydachi\") WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( \"dim_Aggregate_function\".\"*attr_Data_vydachi\")",
"WEEK": "WEEK( \"dim_Aggregate_function\".\"*attr_Data_vydachi\")",
"YEAR": "YEAR( \"dim_Aggregate_function\".\"*attr_Data_vydachi\")",
"YEAR_FOR_WEEK": "toISOYear( \"dim_Aggregate_function\".\"*attr_Data_vydachi\")"
}
},
"sqlTable": "dim_Aggregate_function"
},
"*dim_Danet": {
"columns": {},
"sqlTable": "dim_Danet"
},
"*dim_Day_of_week": {
"columns": {},
"sqlTable": "dim_Day_of_week"
},
"*dim_Filiali": {
"columns": {},
"sqlTable": "dim_Filiali"
},
"*dim_Greidi": {
"columns": {},
"sqlTable": "dim_Greidi"
},
"*dim_Magazin_Goroda": {
"columns": {},
"sqlTable": "dim_Magazin_Goroda"
},
"*dim_Measure_units": {
"columns": {},
"sqlTable": "dim_Measure_units"
},
"*dim_Measures": {
"columns": {},
"sqlTable": "dim_Measures"
},
"*dim_Mediastore_Dokument": {
"columns": {},
"sqlTable": "dim_Mediastore_Dokument"
},
"*dim_Mediastore_Menedzhery": {
"columns": {},
"sqlTable": "dim_Mediastore_Menedzhery"
},
"*dim_Mediastore_Obrascheniya_kl": {
"columns": {},
"sqlTable": "dim_Mediastore_Obrascheniya_kl"
},
"*dim_Mediastore_Otchetnie_pokaz": {
"columns": {},
"sqlTable": "dim_Mediastore_Otchetnie_pokaz"
},
"*dim_Mediastore_Otdeli": {
"columns": {},
"sqlTable": "dim_Mediastore_Otdeli"
},
"*dim_Mediastore_Pokupateli": {
"columns": {},
"sqlTable": "dim_Mediastore_Pokupateli"
},
"*dim_Mediastore_Premii": {
"columns": {},
"sqlTable": "dim_Mediastore_Premii"
},
"*dim_Mediastore_Tip_dannih": {
"columns": {},
"sqlTable": "dim_Mediastore_Tip_dannih"
},
"*dim_Mediastore_Tipi_obrascheni": {
"columns": {},
"sqlTable": "dim_Mediastore_Tipi_obrascheni"
},
"*dim_Obschie_dopuscheniya": {
"columns": {},
"sqlTable": "dim_Obschie_dopuscheniya"
},
"*dim_Plan_prodazh": {
"columns": {},
"sqlTable": "dim_Plan_prodazh"
},
"*dim_Planovie_pokazateli_OS": {
"columns": {},
"sqlTable": "dim_Planovie_pokazateli_OS"
},
"*dim_Planovie_pokazateli_po_per": {
"columns": {},
"sqlTable": "dim_Planovie_pokazateli_po_per"
},
"*dim_Pokazateli_byudzheta_po_ba": {
"columns": {},
"sqlTable": "dim_Pokazateli_byudzheta_po_ba"
},
"*dim_Pol": {
"columns": {},
"sqlTable": "dim_Pol"
},
"*dim_Produkti": {
"columns": {},
"sqlTable": "dim_Produkti"
},
"*dim_Stati_byudzheta_dohodov_i_": {
"columns": {},
"sqlTable": "dim_Stati_byudzheta_dohodov_i_"
},
"*dim_Stati_byudzheta_dvizheniya": {
"columns": {},
"sqlTable": "dim_Stati_byudzheta_dvizheniya"
},
"*dim_Stati_byudzheta_po_balanso": {
"columns": {},
"sqlTable": "dim_Stati_byudzheta_po_balanso"
},
"*dim_Tip_filiala": {
"columns": {},
"sqlTable": "dim_Tip_filiala"
},
"*dim_Versii": {
"columns": {},
"sqlTable": "dim_Versii"
},
"*measureGroup_Magazin_Planovie_": {
"columns": {
"*cal_Kalendar_plan": {
"DATE": "\"*cal_Kalendar_plan\"",
"DAY_NAME": "CASE DAYOFWEEK( \"measureGroup_Magazin_Planovie_\".\"*cal_Kalendar_plan\") WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( \"measureGroup_Magazin_Planovie_\".\"*cal_Kalendar_plan\")",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( \"measureGroup_Magazin_Planovie_\".\"*cal_Kalendar_plan\")",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( \"measureGroup_Magazin_Planovie_\".\"*cal_Kalendar_plan\")",
"HALFYEAR": "CASE QUARTER( \"measureGroup_Magazin_Planovie_\".\"*cal_Kalendar_plan\") WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( \"measureGroup_Magazin_Planovie_\".\"*cal_Kalendar_plan\")",
"MONTH_NAME": "CASE MONTH( \"measureGroup_Magazin_Planovie_\".\"*cal_Kalendar_plan\") WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( \"measureGroup_Magazin_Planovie_\".\"*cal_Kalendar_plan\")",
"WEEK": "WEEK( \"measureGroup_Magazin_Planovie_\".\"*cal_Kalendar_plan\")",
"YEAR": "YEAR( \"measureGroup_Magazin_Planovie_\".\"*cal_Kalendar_plan\")",
"YEAR_FOR_WEEK": "toISOYear( \"measureGroup_Magazin_Planovie_\".\"*cal_Kalendar_plan\")"
}
},
"sqlTable": "measureGroup_Magazin_Planovie_"
},
"*measureGroup_Byudzhet_dohodov_": {
"columns": {
"*cal_Kvartali": {
"DATE": "*cal_Kvartali",
"DAY_NAME": "CASE DAYOFWEEK( \"measureGroup_Byudzhet_dohodov_\".\"*cal_Kvartali\") WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( \"measureGroup_Byudzhet_dohodov_\".\"*cal_Kvartali\")",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( \"measureGroup_Byudzhet_dohodov_\".\"*cal_Kvartali\")",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( \"measureGroup_Byudzhet_dohodov_\".\"*cal_Kvartali\")",
"HALFYEAR": "CASE QUARTER( \"measureGroup_Byudzhet_dohodov_\".\"*cal_Kvartali\") WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( \"measureGroup_Byudzhet_dohodov_\".\"*cal_Kvartali\")",
"MONTH_NAME": "CASE MONTH( \"measureGroup_Byudzhet_dohodov_\".\"*cal_Kvartali\") WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( \"measureGroup_Byudzhet_dohodov_\".\"*cal_Kvartali\")",
"WEEK": "WEEK( \"measureGroup_Byudzhet_dohodov_\".\"*cal_Kvartali\")",
"YEAR": "YEAR( \"measureGroup_Byudzhet_dohodov_\".\"*cal_Kvartali\")",
"YEAR_FOR_WEEK": "toISOYear( \"measureGroup_Byudzhet_dohodov_\".\"*cal_Kvartali\")"
}
},
"sqlTable": "measureGroup_Byudzhet_dohodov_"
},
"*measureGroup_Byudzhet_dvizheni": {
"columns": {
"*cal_Kvartali": {
"DATE": "*cal_Kvartali",
"DAY_NAME": "CASE DAYOFWEEK( \"measureGroup_Byudzhet_dvizheni\".\"*cal_Kvartali\") WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( \"measureGroup_Byudzhet_dvizheni\".\"*cal_Kvartali\")",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( \"measureGroup_Byudzhet_dvizheni\".\"*cal_Kvartali\")",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( \"measureGroup_Byudzhet_dvizheni\".\"*cal_Kvartali\")",
"HALFYEAR": "CASE QUARTER( \"measureGroup_Byudzhet_dvizheni\".\"*cal_Kvartali\") WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( \"measureGroup_Byudzhet_dvizheni\".\"*cal_Kvartali\")",
"MONTH_NAME": "CASE MONTH( \"measureGroup_Byudzhet_dvizheni\".\"*cal_Kvartali\") WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( \"measureGroup_Byudzhet_dvizheni\".\"*cal_Kvartali\")",
"WEEK": "WEEK( \"measureGroup_Byudzhet_dvizheni\".\"*cal_Kvartali\")",
"YEAR": "YEAR( \"measureGroup_Byudzhet_dvizheni\".\"*cal_Kvartali\")",
"YEAR_FOR_WEEK": "toISOYear( \"measureGroup_Byudzhet_dvizheni\".\"*cal_Kvartali\")"
}
},
"sqlTable": "measureGroup_Byudzhet_dvizheni"
},
"*measureGroup_Byudzhet_po_balan": {
"columns": {
"*cal_Kvartal": {
"DATE": "*cal_Kvartal",
"DAY_NAME": "CASE DAYOFWEEK( \"measureGroup_Byudzhet_po_balan\".\"*cal_Kvartal\") WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( \"measureGroup_Byudzhet_po_balan\".\"*cal_Kvartal\")",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( \"measureGroup_Byudzhet_po_balan\".\"*cal_Kvartal\")",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( \"measureGroup_Byudzhet_po_balan\".\"*cal_Kvartal\")",
"HALFYEAR": "CASE QUARTER( \"measureGroup_Byudzhet_po_balan\".\"*cal_Kvartal\") WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( \"measureGroup_Byudzhet_po_balan\".\"*cal_Kvartal\")",
"MONTH_NAME": "CASE MONTH( \"measureGroup_Byudzhet_po_balan\".\"*cal_Kvartal\") WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( \"measureGroup_Byudzhet_po_balan\".\"*cal_Kvartal\")",
"WEEK": "WEEK( \"measureGroup_Byudzhet_po_balan\".\"*cal_Kvartal\")",
"YEAR": "YEAR( \"measureGroup_Byudzhet_po_balan\".\"*cal_Kvartal\")",
"YEAR_FOR_WEEK": "toISOYear( \"measureGroup_Byudzhet_po_balan\".\"*cal_Kvartal\")"
}
},
"sqlTable": "measureGroup_Byudzhet_po_balan"
},
"*measureGroup_Mediastore_Otchet": {
"columns": {
"*cal_Kalendar": {
"DATE": "\"*cal_Kalendar\"",
"DAY_NAME": "CASE DAYOFWEEK( \"measureGroup_Mediastore_Otchet\".\"*cal_Kalendar\") WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( \"measureGroup_Mediastore_Otchet\".\"*cal_Kalendar\")",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( \"measureGroup_Mediastore_Otchet\".\"*cal_Kalendar\")",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( \"measureGroup_Mediastore_Otchet\".\"*cal_Kalendar\")",
"HALFYEAR": "CASE QUARTER( \"measureGroup_Mediastore_Otchet\".\"*cal_Kalendar\") WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( \"measureGroup_Mediastore_Otchet\".\"*cal_Kalendar\")",
"MONTH_NAME": "CASE MONTH( \"measureGroup_Mediastore_Otchet\".\"*cal_Kalendar\") WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( \"measureGroup_Mediastore_Otchet\".\"*cal_Kalendar\")",
"WEEK": "WEEK( \"measureGroup_Mediastore_Otchet\".\"*cal_Kalendar\")",
"YEAR": "YEAR( \"measureGroup_Mediastore_Otchet\".\"*cal_Kalendar\")",
"YEAR_FOR_WEEK": "toISOYear( \"measureGroup_Mediastore_Otchet\".\"*cal_Kalendar\")"
}
},
"sqlTable": "measureGroup_Mediastore_Otchet"
},
"*measureGroup_Mediastore_Prodaz": {
"columns": {
"*cal_Kalendar": {
"DATE": "\"*cal_Kalendar\"",
"DAY_NAME": "CASE DAYOFWEEK( \"measureGroup_Mediastore_Prodaz\".\"*cal_Kalendar\") WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( \"measureGroup_Mediastore_Prodaz\".\"*cal_Kalendar\")",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( \"measureGroup_Mediastore_Prodaz\".\"*cal_Kalendar\")",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( \"measureGroup_Mediastore_Prodaz\".\"*cal_Kalendar\")",
"HALFYEAR": "CASE QUARTER( \"measureGroup_Mediastore_Prodaz\".\"*cal_Kalendar\") WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( \"measureGroup_Mediastore_Prodaz\".\"*cal_Kalendar\")",
"MONTH_NAME": "CASE MONTH( \"measureGroup_Mediastore_Prodaz\".\"*cal_Kalendar\") WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( \"measureGroup_Mediastore_Prodaz\".\"*cal_Kalendar\")",
"WEEK": "WEEK( \"measureGroup_Mediastore_Prodaz\".\"*cal_Kalendar\")",
"YEAR": "YEAR( \"measureGroup_Mediastore_Prodaz\".\"*cal_Kalendar\")",
"YEAR_FOR_WEEK": "toISOYear( \"measureGroup_Mediastore_Prodaz\".\"*cal_Kalendar\")",
"FISCAL_MONTH": "mod((MONTH( \"measureGroup_Mediastore_Prodaz\".\"*cal_Kalendar\") + 3), 12)",
"FISCAL_MONTH_NAME": "CASE mod((MONTH( \"measureGroup_Mediastore_Prodaz\".\"*cal_Kalendar\") + 3), 12) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END"
}
},
"sqlTable": "measureGroup_Mediastore_Prodaz"
},
"*measureGroup_Obschie_dopuschen": {
"columns": {},
"sqlTable": "measureGroup_Obschie_dopuschen"
},
"*measureGroup_Plan_po_OS": {
"columns": {
"*cal_Kvartali": {
"DATE": "*cal_Kvartali",
"DAY_NAME": "CASE DAYOFWEEK( \"measureGroup_Plan_po_OS\".\"*cal_Kvartali\") WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( \"measureGroup_Plan_po_OS\".\"*cal_Kvartali\")",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( \"measureGroup_Plan_po_OS\".\"*cal_Kvartali\")",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( \"measureGroup_Plan_po_OS\".\"*cal_Kvartali\")",
"HALFYEAR": "CASE QUARTER( \"measureGroup_Plan_po_OS\".\"*cal_Kvartali\") WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( \"measureGroup_Plan_po_OS\".\"*cal_Kvartali\")",
"MONTH_NAME": "CASE MONTH( \"measureGroup_Plan_po_OS\".\"*cal_Kvartali\") WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( \"measureGroup_Plan_po_OS\".\"*cal_Kvartali\")",
"WEEK": "WEEK( \"measureGroup_Plan_po_OS\".\"*cal_Kvartali\")",
"YEAR": "YEAR( \"measureGroup_Plan_po_OS\".\"*cal_Kvartali\")",
"YEAR_FOR_WEEK": "toISOYear( \"measureGroup_Plan_po_OS\".\"*cal_Kvartali\")"
}
},
"sqlTable": "measureGroup_Plan_po_OS"
},
"*measureGroup_Plan_po_personalu": {
"columns": {
"*cal_Kvartal": {
"DATE": "*cal_Kvartal",
"DAY_NAME": "CASE DAYOFWEEK( \"measureGroup_Plan_po_personalu\".\"*cal_Kvartal\") WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( \"measureGroup_Plan_po_personalu\".\"*cal_Kvartal\")",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( \"measureGroup_Plan_po_personalu\".\"*cal_Kvartal\")",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( \"measureGroup_Plan_po_personalu\".\"*cal_Kvartal\")",
"HALFYEAR": "CASE QUARTER( \"measureGroup_Plan_po_personalu\".\"*cal_Kvartal\") WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( \"measureGroup_Plan_po_personalu\".\"*cal_Kvartal\")",
"MONTH_NAME": "CASE MONTH( \"measureGroup_Plan_po_personalu\".\"*cal_Kvartal\") WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( \"measureGroup_Plan_po_personalu\".\"*cal_Kvartal\")",
"WEEK": "WEEK( \"measureGroup_Plan_po_personalu\".\"*cal_Kvartal\")",
"YEAR": "YEAR( \"measureGroup_Plan_po_personalu\".\"*cal_Kvartal\")",
"YEAR_FOR_WEEK": "toISOYear( \"measureGroup_Plan_po_personalu\".\"*cal_Kvartal\")"
}
},
"sqlTable": "measureGroup_Plan_po_personalu"
},
"*measureGroup_Plan_prodazh": {
"columns": {
"*cal_Kvartal": {
"DATE": "*cal_Kvartal",
"DAY_NAME": "CASE DAYOFWEEK( \"measureGroup_Plan_prodazh\".\"*cal_Kvartal\") WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( \"measureGroup_Plan_prodazh\".\"*cal_Kvartal\")",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( \"measureGroup_Plan_prodazh\".\"*cal_Kvartal\")",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( \"measureGroup_Plan_prodazh\".\"*cal_Kvartal\")",
"HALFYEAR": "CASE QUARTER( \"measureGroup_Plan_prodazh\".\"*cal_Kvartal\") WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( \"measureGroup_Plan_prodazh\".\"*cal_Kvartal\")",
"MONTH_NAME": "CASE MONTH( \"measureGroup_Plan_prodazh\".\"*cal_Kvartal\") WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( \"measureGroup_Plan_prodazh\".\"*cal_Kvartal\")",
"WEEK": "WEEK( \"measureGroup_Plan_prodazh\".\"*cal_Kvartal\")",
"YEAR": "YEAR( \"measureGroup_Plan_prodazh\".\"*cal_Kvartal\")",
"YEAR_FOR_WEEK": "toISOYear( \"measureGroup_Plan_prodazh\".\"*cal_Kvartal\")"
}
},
"sqlTable": "measureGroup_Plan_prodazh"
},
"Goroda": {
"columns": {},
"sqlTable": "Goroda"
},
"Postavka_tovarov": {
"columns": {
"day_pay": {
"DATE": "day_pay",
"DAY_NAME": "CASE DAYOFWEEK( day_pay) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( day_pay)",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( day_pay)",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( day_pay)",
"HALFYEAR": "CASE QUARTER( day_pay) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( day_pay)",
"MONTH_NAME": "CASE MONTH( day_pay) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( day_pay)",
"WEEK": "WEEK( day_pay)",
"YEAR": "YEAR( day_pay)",
"YEAR_FOR_WEEK": "toISOYear( day_pay)"
},
"fact_delivery_date": {
"DATE": "fact_delivery_date",
"DAY_NAME": "CASE DAYOFWEEK( fact_delivery_date) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( fact_delivery_date)",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( fact_delivery_date)",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( fact_delivery_date)",
"HALFYEAR": "CASE QUARTER( fact_delivery_date) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( fact_delivery_date)",
"MONTH_NAME": "CASE MONTH( fact_delivery_date) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( fact_delivery_date)",
"WEEK": "WEEK( fact_delivery_date)",
"YEAR": "YEAR( fact_delivery_date)",
"YEAR_FOR_WEEK": "toISOYear( fact_delivery_date)"
},
"planned_delivery_date": {
"DATE": "planned_delivery_date",
"DAY_NAME": "CASE DAYOFWEEK( planned_delivery_date) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( planned_delivery_date)",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( planned_delivery_date)",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( planned_delivery_date)",
"HALFYEAR": "CASE QUARTER( planned_delivery_date) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( planned_delivery_date)",
"MONTH_NAME": "CASE MONTH( planned_delivery_date) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( planned_delivery_date)",
"WEEK": "WEEK( planned_delivery_date)",
"YEAR": "YEAR( planned_delivery_date)",
"YEAR_FOR_WEEK": "toISOYear( planned_delivery_date)"
}
},
"sqlTable": "postavka_tovarov"
},
"Prodazhi_avtomobilei": {
"columns": {
"date_of_sale": {
"DATE": "date_of_sale",
"DAY_NAME": "CASE DAYOFWEEK( date_of_sale) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( date_of_sale)",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( date_of_sale)",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( date_of_sale)",
"HALFYEAR": "CASE QUARTER( date_of_sale) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( date_of_sale)",
"MONTH_NAME": "CASE MONTH( date_of_sale) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( date_of_sale)",
"WEEK": "WEEK( date_of_sale)",
"YEAR": "YEAR( date_of_sale)",
"YEAR_FOR_WEEK": "toISOYear( date_of_sale)"
},
"\ncar_brand": {
"OFF": "car_brand"
},
"\ncar_mileage": {
"OFF": "car_mileage"
}
},
"sqlTable": "prodazhi_avtomobilei"
},
"Prodazhi_magazina__rubli": {
"columns": {
"Data_delivery": {
"DATE": "\"prodazhi_magazina__rubli\".\"Data_delivery\"",
"DAY_NAME": "CASE DAYOFWEEK( \"prodazhi_magazina__rubli\".\"Data_delivery\") WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( \"prodazhi_magazina__rubli\".\"Data_delivery\")",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( \"prodazhi_magazina__rubli\".\"Data_delivery\")",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( \"prodazhi_magazina__rubli\".\"Data_delivery\")",
"HALFYEAR": "CASE QUARTER( \"prodazhi_magazina__rubli\".\"Data_delivery\") WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( \"prodazhi_magazina__rubli\".\"Data_delivery\")",
"MONTH_NAME": "CASE MONTH( \"prodazhi_magazina__rubli\".\"Data_delivery\") WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( \"prodazhi_magazina__rubli\".\"Data_delivery\")",
"WEEK": "WEEK( \"prodazhi_magazina__rubli\".\"Data_delivery\")",
"YEAR": "YEAR( \"prodazhi_magazina__rubli\".\"Data_delivery\")",
"YEAR_FOR_WEEK": "toISOYear( \"prodazhi_magazina__rubli\".\"Data_delivery\")"
},
"Data_pay": {
"DATE": "\"prodazhi_magazina__rubli\".\"Data_pay\"",
"DAY_NAME": "CASE DAYOFWEEK( \"prodazhi_magazina__rubli\".\"Data_pay\") WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( \"prodazhi_magazina__rubli\".\"Data_pay\")",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( \"prodazhi_magazina__rubli\".\"Data_pay\")",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( \"prodazhi_magazina__rubli\".\"Data_pay\")",
"HALFYEAR": "CASE QUARTER( \"prodazhi_magazina__rubli\".\"Data_pay\") WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( \"prodazhi_magazina__rubli\".\"Data_pay\")",
"MONTH_NAME": "CASE MONTH( \"prodazhi_magazina__rubli\".\"Data_pay\") WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( \"prodazhi_magazina__rubli\".\"Data_pay\")",
"WEEK": "WEEK( \"prodazhi_magazina__rubli\".\"Data_pay\")",
"YEAR": "YEAR(\"prodazhi_magazina__rubli\".\"Data_pay\")",
"YEAR_FOR_WEEK": "toISOYear( \"prodazhi_magazina__rubli\".\"Data_pay\")",
"FISCAL_MONTH": "mod((MONTH( \"prodazhi_magazina__rubli\".\"Data_pay\") + 3), 12)",
"FISCAL_MONTH_NAME": "CASE mod((MONTH( \"prodazhi_magazina__rubli\".\"Data_pay\") + 3), 12) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END"
}
},
"sqlTable": "prodazhi_magazina__rubli"
},
"Sotrudniki": {
"columns": {
"period_id": {
"DATE": "period_id",
"DAY_NAME": "CASE DAYOFWEEK( period_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH( period_id)",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK( period_id)",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR( period_id)",
"HALFYEAR": "CASE QUARTER( period_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH( period_id)",
"MONTH_NAME": "CASE MONTH( period_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER( period_id)",
"WEEK": "WEEK( period_id)",
"YEAR": "YEAR(period_id)",
"YEAR_FOR_WEEK": "toISOYear( period_id)"
}
},
"sqlTable": "Sotrudniki"
},
"Spravochnik_sotrudnikov": {
"columns": {},
"sqlTable": "Spravochnik_sotrudnikov"
},
"Tovari": {
"columns": {},
"sqlTable": "Tovari"
},
"yb1": {
"columns": {},
"sqlTable": "yb1"
},
"yb2": {
"columns": {},
"sqlTable": "yb2"
}
}
}'
Postgres SQL
Пример настройки
curl --location --request PUT 'http://{ADDRESS}:{PORT}/viqube/sqlbackend' \
--header 'X-API-VERSION: 3.8' \
--header 'Content-Type: application/json' \
--data-raw '{
"enabled": true,
"jdbcOptions": {
"connection": "jdbc:postgresql://127.0.0.1:5432/shop?user={USER}&password={PASSWORD}"
},
"sqlMapping": {
"cities": {
"columns": {
"city": {
"COUNT_WITH_NULL": "city"
}
},
"sqlTable": "cities"
},
"fact_datetime": {
"columns": {},
"sqlTable": ""
},
"fact_rur": {
"columns": {
"discount": {
"COUNT_WITH_NULL": "discount"
},
"period2_id": {
"DATE": "period2_id",
"DAY_NAME": "CASE DATE_PART('\''DOW'\'', period2_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DATE_PART('\''DAY'\'', period2_id)",
"DAY_NUMBER_IN_WEEK": "DATE_PART('\''DOW'\'', period2_id)",
"DAY_NUMBER_IN_YEAR": "DATE_PART('\''DOY'\'', period2_id)",
"HALFYEAR": "CASE DATE_PART('\''QUARTER'\'', period2_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "DATE_PART('\''MONTH'\'', period2_id)",
"MONTH_NAME": "CASE DATE_PART('\''MONTH'\'', period2_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "DATE_PART('\''QUARTER'\'', period2_id)",
"WEEK": "DATE_PART('\''WEEK'\'', period2_id)",
"YEAR": "DATE_PART('\''YEAR'\'', period2_id)",
"YEAR_FOR_WEEK": "DATE_PART('\''ISOYEAR'\'', period2_id)"
},
"period_id": {
"DATE": "period_id",
"DAY_NAME": "CASE DATE_PART('\''DOW'\'', period_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DATE_PART('\''DAY'\'', period_id)",
"DAY_NUMBER_IN_WEEK": "DATE_PART('\''DOW'\'', period_id)",
"DAY_NUMBER_IN_YEAR": "DATE_PART('\''DOY'\'', period_id)",
"HALFYEAR": "CASE DATE_PART('\''QUARTER'\'', period_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "DATE_PART('\''MONTH'\'', period_id)",
"MONTH_NAME": "CASE DATE_PART('\''MONTH'\'', period_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "DATE_PART('\''QUARTER'\'', period_id)",
"WEEK": "DATE_PART('\''WEEK'\'', period_id)",
"YEAR": "DATE_PART('\''YEAR'\'', period_id)",
"YEAR_FOR_WEEK": "DATE_PART('\''ISOYEAR'\'', period_id)"
},
"price": {
"COUNT_WITH_NULL": "price"
}
},
"sqlTable": "fact_rur"
},
"fact_usd": {
"columns": {
"discount": {
"COUNT_WITH_NULL": "discount"
},
"period2_id": {
"DATE": "period2_id",
"DAY_NAME": "CASE DATE_PART('\''DOW'\'', period2_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DATE_PART('\''DAY'\'', period2_id)",
"DAY_NUMBER_IN_WEEK": "DATE_PART('\''DOW'\'', period2_id)",
"DAY_NUMBER_IN_YEAR": "DATE_PART('\''DOY'\'', period2_id)",
"HALFYEAR": "CASE DATE_PART('\''QUARTER'\'', period2_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "DATE_PART('\''MONTH'\'', period2_id)",
"MONTH_NAME": "CASE DATE_PART('\''MONTH'\'', period2_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "DATE_PART('\''QUARTER'\'', period2_id)",
"WEEK": "DATE_PART('\''WEEK'\'', period2_id)",
"YEAR": "DATE_PART('\''YEAR'\'', period2_id)",
"YEAR_FOR_WEEK": "DATE_PART('\''ISOYEAR'\'', period2_id)"
},
"period_id": {
"DATE": "period_id",
"DAY_NAME": "CASE DATE_PART('\''DOW'\'', period_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DATE_PART('\''DAY'\'', period_id)",
"DAY_NUMBER_IN_WEEK": "DATE_PART('\''DOW'\'', period_id)",
"DAY_NUMBER_IN_YEAR": "DATE_PART('\''DOY'\'', period_id)",
"HALFYEAR": "CASE DATE_PART('\''QUARTER'\'', period_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "DATE_PART('\''MONTH'\'', period_id)",
"MONTH_NAME": "CASE DATE_PART('\''MONTH'\'', period_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "DATE_PART('\''QUARTER'\'', period_id)",
"WEEK": "DATE_PART('\''WEEK'\'', period_id)",
"YEAR": "DATE_PART('\''YEAR'\'', period_id)",
"YEAR_FOR_WEEK": "DATE_PART('\''ISOYEAR'\'', period_id)"
},
"price": {
"COUNT_WITH_NULL": "price"
}
},
"sqlTable": "fact_usd"
},
"fact_workers": {
"columns": {
"period_id": {
"DATE": "period_id",
"DAY_NAME": "CASE DATE_PART('\''DOW'\'', period_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DATE_PART('\''DAY'\'', period_id)",
"DAY_NUMBER_IN_WEEK": "DATE_PART('\''DOW'\'', period_id)",
"DAY_NUMBER_IN_YEAR": "DATE_PART('\''DOY'\'', period_id)",
"HALFYEAR": "CASE DATE_PART('\''QUARTER'\'', period_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "DATE_PART('\''MONTH'\'', period_id)",
"MONTH_NAME": "CASE DATE_PART('\''MONTH'\'', period_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "DATE_PART('\''QUARTER'\'', period_id)",
"WEEK": "DATE_PART('\''WEEK'\'', period_id)",
"YEAR": "DATE_PART('\''YEAR'\'', period_id)",
"YEAR_FOR_WEEK": "DATE_PART('\''ISOYEAR'\'', period_id)"
}
},
"sqlTable": "fact_workers"
},
"goods": {
"columns": {
"category": {
"COUNT_WITH_NULL": "category"
},
"name": {
"COUNT_WITH_NULL": "name"
}
},
"sqlTable": "goods"
},
"workers": {
"columns": {},
"sqlTable": ""
}
}
}'
Vertica
Пример настройки
curl --location --request PUT 'http://{ADDRESS}:{PORT}/viqube/sqlbackend' \
--header 'X-API-VERSION: 3.8' \
--header 'Content-Type: application/json' \
--data-raw '{
"enabled": true,
"jdbcOptions": {
"connection": "jdbc:vertica://{ADDRESS}:{PORT}/shop?user={USER}",
"isResetUpdateTime": true
},
"sqlMapping": {
"cities": {
"columns": {
"city": {
"COUNT_WITH_NULL": "city"
}
},
"sqlTable": "cities"
},
"fact_datetime": {
"columns": {},
"sqlTable": ""
},
"fact_rur": {
"columns": {
"discount": {
"COUNT_WITH_NULL": "discount"
},
"period2_id": {
"DATE": "period2_id",
"DAY_NAME": "CASE DATE_PART('DOW', period2_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DATE_PART('DAY', period2_id)",
"DAY_NUMBER_IN_WEEK": "DATE_PART('DOW', period2_id)",
"DAY_NUMBER_IN_YEAR": "DATE_PART('DOY', period2_id)",
"HALFYEAR": "CASE DATE_PART('QUARTER', period2_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "DATE_PART('MONTH', period2_id)",
"MONTH_NAME": "CASE DATE_PART('MONTH', period2_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "DATE_PART('QUARTER', period2_id)",
"WEEK": "DATE_PART('WEEK', period2_id)",
"YEAR": "DATE_PART('YEAR', period2_id)",
"YEAR_FOR_WEEK": "DATE_PART('ISOYEAR', period2_id)"
},
"period_id": {
"DATE": "period_id",
"DAY_NAME": "CASE DATE_PART('DOW', period_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DATE_PART('DAY', period_id)",
"DAY_NUMBER_IN_WEEK": "DATE_PART('DOW', period_id)",
"DAY_NUMBER_IN_YEAR": "DATE_PART('DOY', period_id)",
"HALFYEAR": "CASE DATE_PART('QUARTER', period_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "DATE_PART('MONTH', period_id)",
"MONTH_NAME": "CASE DATE_PART('MONTH', period_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "DATE_PART('QUARTER', period_id)",
"WEEK": "DATE_PART('WEEK', period_id)",
"YEAR": "DATE_PART('YEAR', period_id)",
"YEAR_FOR_WEEK": "DATE_PART('ISOYEAR', period_id)"
},
"price": {
"COUNT_WITH_NULL": "price"
}
},
"sqlTable": "fact_rur"
},
"fact_usd": {
"columns": {
"discount": {
"COUNT_WITH_NULL": "discount"
},
"period2_id": {
"DATE": "period2_id",
"DAY_NAME": "CASE DATE_PART('DOW', period2_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DATE_PART('DAY', period2_id)",
"DAY_NUMBER_IN_WEEK": "DATE_PART('DOW', period2_id)",
"DAY_NUMBER_IN_YEAR": "DATE_PART('DOY', period2_id)",
"HALFYEAR": "CASE DATE_PART('QUARTER', period2_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "DATE_PART('MONTH', period2_id)",
"MONTH_NAME": "CASE DATE_PART('MONTH', period2_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "DATE_PART('QUARTER', period2_id)",
"WEEK": "DATE_PART('WEEK', period2_id)",
"YEAR": "DATE_PART('YEAR', period2_id)",
"YEAR_FOR_WEEK": "DATE_PART('ISOYEAR', period2_id)"
},
"period_id": {
"DATE": "period_id",
"DAY_NAME": "CASE DATE_PART('DOW', period_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DATE_PART('DAY', period_id)",
"DAY_NUMBER_IN_WEEK": "DATE_PART('DOW', period_id)",
"DAY_NUMBER_IN_YEAR": "DATE_PART('DOY', period_id)",
"HALFYEAR": "CASE DATE_PART('QUARTER', period_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "DATE_PART('MONTH', period_id)",
"MONTH_NAME": "CASE DATE_PART('MONTH', period_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "DATE_PART('QUARTER', period_id)",
"WEEK": "DATE_PART('WEEK', period_id)",
"YEAR": "DATE_PART('YEAR', period_id)",
"YEAR_FOR_WEEK": "DATE_PART('ISOYEAR', period_id)"
},
"price": {
"COUNT_WITH_NULL": "price"
}
},
"sqlTable": "fact_usd"
},
"fact_workers": {
"columns": {
"period_id": {
"DATE": "period_id",
"DAY_NAME": "CASE DATE_PART('DOW', period_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DATE_PART('DAY', period_id)",
"DAY_NUMBER_IN_WEEK": "DATE_PART('DOW', period_id)",
"DAY_NUMBER_IN_YEAR": "DATE_PART('DOY', period_id)",
"HALFYEAR": "CASE DATE_PART('QUARTER', period_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "DATE_PART('MONTH', period_id)",
"MONTH_NAME": "CASE DATE_PART('MONTH', period_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "DATE_PART('QUARTER', period_id)",
"WEEK": "DATE_PART('WEEK', period_id)",
"YEAR": "DATE_PART('YEAR', period_id)",
"YEAR_FOR_WEEK": "DATE_PART('ISOYEAR', period_id)"
}
},
"sqlTable": "fact_workers"
},
"goods": {
"columns": {
"category": {
"COUNT_WITH_NULL": "category"
},
"name": {
"COUNT_WITH_NULL": "name"
}
},
"sqlTable": "goods"
},
"workers": {
"columns": {},
"sqlTable": ""
}
}
}
Greenplum
Пример настройки
curl --location --request PUT 'http://{ADDRESS}:{PORT}/viqube/sqlbackend' \
--header 'X-API-VERSION: 3.8' \
--header 'Content-Type: application/json' \
--data-raw '{
"enabled": true,
"jdbcOptions": {
"connection": "jdbc:postgresql://{ADDRESS}:{PORT}/shop?user={USER}&password={PASSWORD}"
},
"sqlMapping": {
"cities": {
"columns": {
"city": {
"COUNT_WITH_NULL": "city"
}
},
"sqlTable": "cities"
},
"fact_datetime": {
"columns": {},
"sqlTable": ""
},
"fact_rur": {
"columns": {
"discount": {
"COUNT_WITH_NULL": "discount"
},
"period2_id": {
"DATE": "period2_id",
"DAY_NAME": "CASE DATE_PART('\''DOW'\'', period2_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DATE_PART('\''DAY'\'', period2_id)",
"DAY_NUMBER_IN_WEEK": "DATE_PART('\''DOW'\'', period2_id)",
"DAY_NUMBER_IN_YEAR": "DATE_PART('\''DOY'\'', period2_id)",
"HALFYEAR": "CASE DATE_PART('\''QUARTER'\'', period2_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "DATE_PART('\''MONTH'\'', period2_id)",
"MONTH_NAME": "CASE DATE_PART('\''MONTH'\'', period2_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "DATE_PART('\''QUARTER'\'', period2_id)",
"WEEK": "DATE_PART('\''WEEK'\'', period2_id)",
"YEAR": "DATE_PART('\''YEAR'\'', period2_id)",
"YEAR_FOR_WEEK": "DATE_PART('\''ISOYEAR'\'', period2_id)"
},
"period_id": {
"DATE": "period_id",
"DAY_NAME": "CASE DATE_PART('\''DOW'\'', period_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DATE_PART('\''DAY'\'', period_id)",
"DAY_NUMBER_IN_WEEK": "DATE_PART('\''DOW'\'', period_id)",
"DAY_NUMBER_IN_YEAR": "DATE_PART('\''DOY'\'', period_id)",
"HALFYEAR": "CASE DATE_PART('\''QUARTER'\'', period_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "DATE_PART('\''MONTH'\'', period_id)",
"MONTH_NAME": "CASE DATE_PART('\''MONTH'\'', period_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "DATE_PART('\''QUARTER'\'', period_id)",
"WEEK": "DATE_PART('\''WEEK'\'', period_id)",
"YEAR": "DATE_PART('\''YEAR'\'', period_id)",
"YEAR_FOR_WEEK": "DATE_PART('\''ISOYEAR'\'', period_id)"
},
"price": {
"COUNT_WITH_NULL": "price"
}
},
"sqlTable": "fact_rur"
},
"fact_usd": {
"columns": {
"discount": {
"COUNT_WITH_NULL": "discount"
},
"period2_id": {
"DATE": "period2_id",
"DAY_NAME": "CASE DATE_PART('\''DOW'\'', period2_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DATE_PART('\''DAY'\'', period2_id)",
"DAY_NUMBER_IN_WEEK": "DATE_PART('\''DOW'\'', period2_id)",
"DAY_NUMBER_IN_YEAR": "DATE_PART('\''DOY'\'', period2_id)",
"HALFYEAR": "CASE DATE_PART('\''QUARTER'\'', period2_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "DATE_PART('\''MONTH'\'', period2_id)",
"MONTH_NAME": "CASE DATE_PART('\''MONTH'\'', period2_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "DATE_PART('\''QUARTER'\'', period2_id)",
"WEEK": "DATE_PART('\''WEEK'\'', period2_id)",
"YEAR": "DATE_PART('\''YEAR'\'', period2_id)",
"YEAR_FOR_WEEK": "DATE_PART('\''ISOYEAR'\'', period2_id)"
},
"period_id": {
"DATE": "period_id",
"DAY_NAME": "CASE DATE_PART('\''DOW'\'', period_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DATE_PART('\''DAY'\'', period_id)",
"DAY_NUMBER_IN_WEEK": "DATE_PART('\''DOW'\'', period_id)",
"DAY_NUMBER_IN_YEAR": "DATE_PART('\''DOY'\'', period_id)",
"HALFYEAR": "CASE DATE_PART('\''QUARTER'\'', period_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "DATE_PART('\''MONTH'\'', period_id)",
"MONTH_NAME": "CASE DATE_PART('\''MONTH'\'', period_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "DATE_PART('\''QUARTER'\'', period_id)",
"WEEK": "DATE_PART('\''WEEK'\'', period_id)",
"YEAR": "DATE_PART('\''YEAR'\'', period_id)",
"YEAR_FOR_WEEK": "DATE_PART('\''ISOYEAR'\'', period_id)"
},
"price": {
"COUNT_WITH_NULL": "price"
}
},
"sqlTable": "fact_usd"
},
"fact_workers": {
"columns": {
"period_id": {
"DATE": "period_id",
"DAY_NAME": "CASE DATE_PART('\''DOW'\'', period_id) WHEN 1 THEN 'Понедельник' WHEN 2 THEN 'Вторник' WHEN 3 THEN 'Среда' WHEN 4 THEN 'Четверг' WHEN 5 THEN 'Пятница' WHEN 6 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DATE_PART('\''DAY'\'', period_id)",
"DAY_NUMBER_IN_WEEK": "DATE_PART('\''DOW'\'', period_id)",
"DAY_NUMBER_IN_YEAR": "DATE_PART('\''DOY'\'', period_id)",
"HALFYEAR": "CASE DATE_PART('\''QUARTER'\'', period_id) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "DATE_PART('\''MONTH'\'', period_id)",
"MONTH_NAME": "CASE DATE_PART('\''MONTH'\'', period_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "DATE_PART('\''QUARTER'\'', period_id)",
"WEEK": "DATE_PART('\''WEEK'\'', period_id)",
"YEAR": "DATE_PART('\''YEAR'\'', period_id)",
"YEAR_FOR_WEEK": "DATE_PART('\''ISOYEAR'\'', period_id)"
}
},
"sqlTable": "fact_workers"
},
"goods": {
"columns": {
"category": {
"COUNT_WITH_NULL": "category"
},
"name": {
"COUNT_WITH_NULL": "name"
}
},
"sqlTable": "goods"
},
"workers": {
"columns": {},
"sqlTable": ""
}
}
}'
MySQL
Пример настройки
{
"enabled": true,
"jdbcOptions": {
"connection": "jdbc:mysql://{ADDRESS}:{PORT}/public?user={USER}&password={PASSWORD}",
"isResetUpdateTime": true
},
"sqlMapping": {
"cities": {
"columns": {
"city": {
"COUNT_WITH_NULL": "city"
}
},
"sqlTable": "cities"
},
"fact_datetime": {
"columns": {},
"sqlTable": ""
},
"fact_rur": {
"columns": {
"discount": {
"COUNT_WITH_NULL": "discount"
},
"period2_id": {
"DATE": "period2_id",
"DAY_NAME": "CASE DAYOFWEEK(period2_id) WHEN 2 THEN 'Понедельник' WHEN 3 THEN 'Вторник' WHEN 4 THEN 'Среда' WHEN 5 THEN 'Четверг' WHEN 6 THEN 'Пятница' WHEN 7 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH(period2_id)",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK(period2_id)",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR(period2_id)",
"HALFYEAR": "CASE floor(quarter(period2_id) / 3) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH(period2_id)",
"MONTH_NAME": "CASE MONTH(period2_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER(period2_id)",
"WEEK": "WEEK(period2_id)",
"YEAR": "YEAR(period2_id)",
"YEAR_FOR_WEEK": "YEARWEEK(period2_id)"
},
"period_id": {
"DATE": "period_id",
"DAY_NAME": "CASE DAYOFWEEK(period_id) WHEN 2 THEN 'Понедельник' WHEN 3 THEN 'Вторник' WHEN 4 THEN 'Среда' WHEN 5 THEN 'Четверг' WHEN 6 THEN 'Пятница' WHEN 7 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH(period_id)",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK(period_id)",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR(period_id)",
"HALFYEAR": "CASE floor(quarter(period_id) / 3) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH(period_id)",
"MONTH_NAME": "CASE MONTH(period_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER(period_id)",
"WEEK": "WEEK(period_id)",
"YEAR": "YEAR(period_id)",
"YEAR_FOR_WEEK": "YEARWEEK(period_id)"
},
"price": {
"COUNT_WITH_NULL": "price"
}
},
"sqlTable": "fact_rur"
},
"fact_usd": {
"columns": {
"discount": {
"COUNT_WITH_NULL": "discount"
},
"period2_id": {
"DATE": "period2_id",
"DAY_NAME": "CASE DAYOFWEEK(period2_id) WHEN 2 THEN 'Понедельник' WHEN 3 THEN 'Вторник' WHEN 4 THEN 'Среда' WHEN 5 THEN 'Четверг' WHEN 6 THEN 'Пятница' WHEN 7 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH(period2_id)",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK(period2_id)",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR(period2_id)",
"HALFYEAR": "CASE floor(quarter(period2_id) / 3) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH(period2_id)",
"MONTH_NAME": "CASE MONTH(period2_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER(period2_id)",
"WEEK": "WEEK(period2_id)",
"YEAR": "YEAR(period2_id)",
"YEAR_FOR_WEEK": "YEARWEEK(period2_id)"
},
"period_id": {
"DATE": "period_id",
"DAY_NAME": "CASE DAYOFWEEK(period_id) WHEN 2 THEN 'Понедельник' WHEN 3 THEN 'Вторник' WHEN 4 THEN 'Среда' WHEN 5 THEN 'Четверг' WHEN 6 THEN 'Пятница' WHEN 7 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH(period_id)",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK(period_id)",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR(period_id)",
"HALFYEAR": "CASE floor(quarter(period_id) / 3) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH(period_id)",
"MONTH_NAME": "CASE MONTH(period_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER(period_id)",
"WEEK": "WEEK(period_id)",
"YEAR": "YEAR(period_id)",
"YEAR_FOR_WEEK": "YEARWEEK(period_id)"
},
"price": {
"COUNT_WITH_NULL": "price"
}
},
"sqlTable": "fact_usd"
},
"fact_workers": {
"columns": {
"period_id": {
"DATE": "period_id",
"DAY_NAME": "CASE DAYOFWEEK(period_id) WHEN 2 THEN 'Понедельник' WHEN 3 THEN 'Вторник' WHEN 4 THEN 'Среда' WHEN 5 THEN 'Четверг' WHEN 6 THEN 'Пятница' WHEN 7 THEN 'Суббота' ELSE 'Воскресенье' END",
"DAY_NUMBER_IN_MONTH": "DAYOFMONTH(period_id)",
"DAY_NUMBER_IN_WEEK": "DAYOFWEEK(period_id)",
"DAY_NUMBER_IN_YEAR": "DAYOFYEAR(period_id)",
"HALFYEAR": "CASE floor(quarter(period_id) / 3) WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 2 END",
"MONTH": "MONTH(period_id)",
"MONTH_NAME": "CASE MONTH(period_id) WHEN 1 THEN 'Январь' WHEN 2 THEN 'Февраль' WHEN 3 THEN 'Март' WHEN 4 THEN 'Апрель' WHEN 5 THEN 'Май' WHEN 6 THEN 'Июнь' WHEN 7 THEN 'Июль' WHEN 8 THEN 'Август' WHEN 9 THEN 'Сентябрь' WHEN 10 THEN 'Октябрь' WHEN 11 THEN 'Ноябрь' ELSE 'Декабрь' END",
"QUARTER": "QUARTER(period_id)",
"WEEK": "WEEK(period_id)",
"YEAR": "YEAR(period_id)",
"YEAR_FOR_WEEK": "YEARWEEK(period_id)"
}
},
"sqlTable": "fact_workers"
},
"goods": {
"columns": {
"category": {
"COUNT_WITH_NULL": "category"
},
"name": {
"COUNT_WITH_NULL": "name"
}
},
"sqlTable": "goods"
},
"workers": {
"columns": {},
"sqlTable": ""
}
}
}
- Нет меток