applies to: Indyco Builder 2.0 +

feature available on: 

Enterprise Edition

indyco lets you can generate the DDL script of your data warehouse with a simple wizard, also on Hadoop clusters.

From menu Tools > Logical Modeling > Hadoop you can start with a Hadoop wizard.


Step 1: Select Target

As a first step, choose the target platform and the default storage type for your files (Impala).

You can override the storage type for each tables in further steps.



Step 2: Edit Preferences

In the preferences section you can override the default conventions of your data warehouse (prefixes and default types), or just keep the default ones.



Step 3: Choose datamarts

Choose the datamarts you want to export. Every conformed hierarchies will be exported as well.



Step 4: Database options

Insert a target database name and choose if you want that indyco creates it from scratch or not.



Step 5: Export Options

Before generating your script, you can review the result of the logical modeling and fine tune it if needed.

You will be able to override every setting of your tables: declare them as external, store into different locations, or change their storage type.




Finish
Clicking on finish, indyco will generate a DDL script that you can execute into your preferred Hadoop manager


/*
-- ----------------------------------------------------------------
--          Indyco Builder - Auto-Generated DDL Script           --
-- ----------------------------------------------------------------
--
-- Project Name . . . . . . : Indyco Tutorial
--  + Exported Datamarts. . : Commercial, Inventory
--  + Export Timestamp. . . : 2015-06-17 15:06:01
--
-- Selected Profile . . . . : Star Schema
-- Create database  . . . . : True
-- Database name  . . . . . : indyco_sample_project
--
-- Target DB. . . . . . . . : Impala
--
-- ----------------------------------------------------------------
*/

CREATE DATABASE `indyco_sample_project` LOCATION '/path/of/my/db';

CREATE TABLE `indyco_sample_project`.`DT_AGENT`
(
	`ID_AGENT` INT,
	`AGENT` STRING,
	`AREA_MANAGER` STRING,
	`ID_AREA_MANAGER` INT
)
STORED AS AVRO
TBLPROPERTIES (
'avro.schema.literal'='{
"type": "record",
"name": "DT_AGENT",
"fields": [
{"name": "ID_AGENT", "type": "int"},
{"name": "AGENT", "type": "string"},
{"name": "AREA_MANAGER", "type": "string"},
{"name": "ID_AREA_MANAGER", "type": "int"}
]}');

CREATE EXTERNAL TABLE `indyco_sample_project`.`BT_AGENT_CONTROLLER`
(
	`ID_AGENT` INT,
	`ID_CONTROLLER` INT,
	`WEIGHT` INT
)
STORED AS PARQUET
LOCATION '/different/path';

CREATE TABLE `indyco_sample_project`.`DT_AGENT_CONTROLLER`
(
	`ID_CONTROLLER` INT,
	`CONTROLLER` STRING
)
STORED AS PARQUET;