Differences

This shows you the differences between two versions of the page.

--- admin:features:snapshots:creation [2012/03/29 22:28]
doc
+++ admin:features:snapshots:creation [2012/03/30 21:36]
doc
@@ Line 14: / Line 14: @@
 ==== Creating Omnidex Snapshots ====
-Omnidex Snapshots are usually created by exporting data from an underlying database.  This can be done using Omnidex's EXPORT command, or it can be done using any database tool that can generate a raw data file or a delimited file.
+Omnidex Snapshots are usually created by exporting data from an underlying database; however, they can also be created by combining data from any source.  Omnidex Snapshots can be created using the Omnidex EXPORT statement, or they can be create using any database or ETL tool that can create a raw data file or a delimited file.
+=== Using Omnidex's EXPORT Statement ===
+The process of creating an Omnidex Snapshot begins with creating an Omnidex Environment File that references the underlying relational database.  This will allow Omnidex to access the underlying database.  There is no need to add Omnidex indexing to this particular Omnidex Environment since it will only be used to export data.  Refer to the documentation for [[integration:rdbms:oracle:home|Oracle]], [[integration:rdbms:sqlserver:home|SQL Server]], or [[integration:rdbms:mysql:home|MySQL]] for instructions to create this Omnidex Environment.
+Once Omnidex can access the underlying relational database, use the EXPORT statement to export data.  This can be as simple as exporting the entire table ...
+<code>
+export (select * from INDIVIDUALS) to dat/individuals.dat;
+</code>
+... or it can use more complex statements that denormalize, aggregate, join or filter data.  The following example creates a denormalized table from a subset of rows in several joined tables, including new columns generated from SQL expressions.
+<code>
+export
+  (
+  select        INDIVIDUALS.INDIVIDUAL,
+                HOUSEHOLDS.HOUSEHOLD,
+                HOUSEHOLDS.ADDRESS,
+                HOUSEHOLDS.CITY,
+                HOUSEHOLDS.STATE,
+                STATES.REGION,
+                STATES.TAX_RATE,
+                HOUSEHOLDS.ZIP,
+                HOUSEHOLDS.COUNTRY,
+                INDIVIDUALS.NAME,
+                INDIVIDUALS.GENDER,
+                INDIVIDUALS.BIRTHDATE,
+                cast($compare_dates(INDIVIDUALS.BIRTHDATE, current_date, 'YY')
+                  as integer) AGE,
+                cast(case
+                  when $compare_dates(INDIVIDUALS.BIRTHDATE, current_date, 'YY')
+                    between 0 and 17 then '0-17'
+                  when $compare_dates(INDIVIDUALS.BIRTHDATE, current_date, 'YY')
+                    between 18 and 29 then '18-29'
+                  when $compare_dates(INDIVIDUALS.BIRTHDATE, current_date, 'YY')
+                    between 30 and 39 then '30-39'
+                  when $compare_dates(INDIVIDUALS.BIRTHDATE, current_date, 'YY')
+                    between 40 and 49 then '40-49'
+                  when $compare_dates(INDIVIDUALS.BIRTHDATE, current_date, 'YY')
+                    between 50 and 59 then '50-59'
+                  else '60+'
+                end as character(10)) AGE_GROUP,
+                INDIVIDUALS.PHONE,
+                cast(substring(INDIVIDUALS.PHONE from 2 for 3)
+                  as character(3)) PHONE_AREACODE,
+                cast(substring(INDIVIDUALS.PHONE from 7 for 3)
+                  as character(3)) PHONE_PREFIX,
+                cast(substring(INDIVIDUALS.PHONE from 11 for 4)
+                  as character(4)) PHONE_SUFFIX,
+                INDIVIDUALS.EMAIL,
+                cast(case
+                  when position('@' in INDIVIDUALS.EMAIL) > 0 then
+                    substring(INDIVIDUALS.EMAIL from 1 for
+                      position('@' in INDIVIDUALS.EMAIL) - 1)
+                  else ''
+                end as character(60)) EMAIL_MAILBOX,
+                cast(case
+                  when position('@' in INDIVIDUALS.EMAIL) > 0 then
+                    substring(INDIVIDUALS.EMAIL
+                      from position('@' in INDIVIDUALS.EMAIL) + 1)
+                  else ''
+                end as character(60)) EMAIL_DOMAIN
+    from        INDIVIDUALS
+    join        HOUSEHOLDS on INDIVIDUALS.HOUSEHOLD =
+                HOUSEHOLDS.HOUSEHOLD
+    join        STATES on HOUSEHOLDS.STATE = STATES.STATE
+    join        COUNTRIES on STATES.COUNTRY = COUNTRIES.COUNTRY
+    join        GENDERS on INDIVIDUALS.GENDER = GENDERS.GENDER
+    where       HOUSEHOLDS.COUNTRY = 'US' and
+                INDIVIDUALS.BIRTHDATE <= 'December 31, 1999'
+  )
+  to          dat/simple_view.dat;
+</code>
+This process is repeated for all tables that will reside in the Omnidex Snapshot.  Omnidex the tables are exported, a new Omnidex Environment File is created around these exported files.  This Omnidex Environment is usually heavily indexed to provide a high-performing query environment.
+<code>
+create environment
+ in                   "simple_view.xml"
+ with                 delete;
+create database       "SIMPLE_VIEW"
+  type                FILE
+  index_directory     "idx"
+ in                   "simple_view.xml";
+create table          "COUNTRIES"
+ physical             "dat/cnt.dat"
+ (
+  "COUNTRY"           CHARACTER(2)      omnidex,
+  "DESCRIPTION"       STRING(47)        quicktext,
+  "LATITUDE"          FLOAT             omnidex      usage "LATITUDE",
+  "LONGITUDE"         FLOAT             omnidex      usage "LONGITUDE",
+  "CAPITAL"           STRING(31)        quicktext,
+  "CAPITAL_LAT"       FLOAT             omnidex      usage "LATITUDE",
+  "CAPITAL_LONG"      FLOAT             omnidex      usage "LONGITUDE",
+  constraint COUNTRIES_COUNTRY_PK primary ("COUNTRY")
+ )
+ in                   "simple_view.xml";
+create table          "STATES"
+ physical             "dat/sta.dat"
+ (
+  "STATE"             CHARACTER(2)      omnidex,
+  "DESCRIPTION"       STRING(31)        quicktext,
+  "STATE_CODE"        CHARACTER(2)      omnidex,
+  "REGION"            CHARACTER(2)      omnidex,
+  "COUNTRY"           CHARACTER(2)      omnidex,
+  "TAX_RATE"          FLOAT             omnidex,
+  constraint STATES_STATE_PK primary ("STATE"),
+  constraint STATES_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES"
+ )
+ in                   "simple_view.xml";
+create table          "GENDERS"
+ physical             "dat/gdr.dat"
+ (
+  "GENDER"            CHARACTER(1)      omnidex,
+  "DESCRIPTION"       STRING(31)        quicktext,
+  constraint GENDERS_GENDER_PK primary ("GENDER")
+ )
+ in                   "simple_view.xml";
+create table          "HOUSEHOLDS"
+ physical             "dat/households.dat"
+ (
+  "HOUSEHOLD"         CHARACTER(12)     omnidex,
+  "ADDRESS"           CHARACTER(50)     quicktext,
+  "CITY"              CHARACTER(28)     quicktext,
+  "STATE"             CHARACTER(2)      omnidex,
+  "ZIP"               CHARACTER(5)      omnidex,
+  "COUNTRY"           CHARACTER(2)      omnidex,
+  constraint HOUSEHOLDS_HOUSEHOLD_PK primary ("HOUSEHOLD"),
+  constraint HOUSEHOLDS_STATE_FK foreign ("STATE") references "STATES",
+  constraint HOUSEHOLDS_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES"
+ )
+ in                   "simple_view.xml";
+create table          "INDIVIDUALS"
+ physical             "dat/individuals.dat"
+ (
+  "INDIVIDUAL"        CHARACTER(12)     omnidex,
+  "HOUSEHOLD"         CHARACTER(12)     omnidex,
+  "NAME"              CHARACTER(50)     quicktext,
+  "GENDER"            CHARACTER(1)      omnidex bitmap,
+  "BIRTHDATE"         ANSI DATE         omnidex,
+  "PHONE"             CHARACTER(14)     omnidex,
+  "EMAIL"             CHARACTER(60)     quicktext,
+  constraint INDIVIDUALS_INDIVIDUAL_PK primary ("INDIVIDUAL"),
+  constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS",
+  constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS",
+ )
+ in                   "simple_view.xml";
+create table          "SIMPLE_VIEW"
+ physical             "dat/simple_view.dat"
+ (
+  "INDIVIDUAL"        CHARACTER(12)     omnidex,
+  "HOUSEHOLD"         CHARACTER(12)     omnidex,
+  "ADDRESS"           CHARACTER(50)     omnidex,
+  "CITY"              CHARACTER(28)     omnidex,
+  "STATE"             CHARACTER(2)      omnidex,
+  "REGION"            CHARACTER(2)      omnidex,
+  "TAX_RATE"          FLOAT             omnidex,
+  "ZIP"               CHARACTER(5)      omnidex,
+  "COUNTRY"           CHARACTER(2)      omnidex,
+  "NAME"              CHARACTER(50)     omnidex,
+  "GENDER"            CHARACTER(1)      omnidex bitmap,
+  "BIRTHDATE"         ANSI DATE         omnidex,
+  "AGE"               INTEGER           omnidex,
+  "AGE_GROUP"         CHARACTER(10)     omnidex bitmap,
+  "PHONE"             CHARACTER(14)     omnidex,
+  "PHONE_AREACODE"    CHARACTER(3)      omnidex,
+  "PHONE_PREFIX"      CHARACTER(3)      omnidex,
+  "PHONE_SUFFIX"      CHARACTER(4)      omnidex,
+  "EMAIL"             CHARACTER(60)     omnidex,
+  "EMAIL_MAILBOX"     CHARACTER(60)     omnidex,
+  "EMAIL_DOMAIN"      CHARACTER(60)     omnidex,
+  constraint VIEW_INDIVIDUAL_PK primary ("INDIVIDUAL"),
+  constraint VIEW_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS",
+  constraint VIEW_GENDER_FK foreign ("GENDER") references "GENDERS",
+  constraint VIEW_STATE_FK foreign ("STATE") references "STATES",
+  constraint VIEW_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES"
+ )
+ in                   "simple_view.xml";
+</code>
+This Omnidex Environment is now ready for indexes to be built and for queries to be run.  At this point, it is the same as a standard, raw data file environment.  Refer to the section on [[integration:rawdata:home|Raw Data Files]] for detailed documentation.
+=== Using a Database or ETL Tool ===
+When creating an Omnidex Snapshot using a database or ETL tool, simply request that the data be extracted into a raw data file or a delimited file.  A raw data file is a binary stream of data, with fixed-length records using native datatypes.  A delimited file uses delimiters to identify when one column ends and another begins, and similarly when one row ends and the next row begins.  Refer to the section on [[integration:rawdata:home|Raw Data Files]] for detailed documentation.

Back to top

admin/features/snapshots/creation.txt · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes