Integration: Relational Databases

Generating Raw Data Files

There are many ways to create raw data files. Omnidex is especially versatile at accessing data files from many different external sources. Omnidex can also be used to create raw data files from another data source using standard SQL statements.

External Sources for Raw Data Files

Raw data files are commonly generated from relational databases, third party tools, vendor subscriptions and applications.

  • RDBMS Exports - Relational databases and office products often have export utilities that create raw data files. These may be delimited files or fixed-length data files.
  • Third-party Tools - Extract, transformation and loading (ETL) tools can produce a wide variety of delimited and fixed-length data files,
  • Vendor Subscriptions - Vendors often distribute their data using delimited or fixed-length data files, either as one-time distributions or regular subscriptions.
  • Application - Applications often generate log files that are valuable to index and search with Omnidex.

Exporting Raw Data using Omnidex SQL

Omnidex SQL statements can be used to generate different types of raw data files. The EXPORT statement can be used to convert the results of any SQL statement into raw data files supported by Omnidex. These EXPORT statements can be as simple as exporting all of the columns from a table, or as complex as joining multiple tables with complex aggregations and expressions.

In the examples below, Omnidex EXPORT statements are used to create raw data files. These examples create fixed-length data files, but there are options to cause files to be created using different types. Consult the Integration Guides to see instructions for exporting data to each type of file.

Omnidex SQL Example 1: Export of a table

This example exports all of the columns and rows from a table.

export        (select        *
                 from        INDIVIDUALS)
  to          individuals.dat
  with        DELETE;
Omnidex SQL Example 2: Denormalization of two tables

This example joins two tables and exports specific columns, applying criteria to restrict the rows returned. It also uses SQL functions to create new columns.

export        (select        I.NAME,
                             H.ADDRESS,
                             H.CITY,
                             H.STATE,
                             H.ZIP,
                             I.PHONE,
                             I.EMAIL,
                             $compare_dates(I.BIRTHDAY, current_date, 'YEARS') AGE
                 from        INDIVIDUALS I
                 join        HOUSEHOLDS H on I.HOUSEHOLD = H.HOUSEHOLD
                 where       H.STATE = 'CA')
  to          CA_individuals.dat
  with        DELETE;
Omnidex SQL Example 3: Summarization of two tables

This example joins two tables and exports aggregations to form a summary table.

export        (select        H.STATE,
                             H.ZIP,
                             count(distinct H.HOUSEHOLD),
                             count(*)
                 from        INDIVIDUALS I
                 join        HOUSEHOLDS H on I.HOUSEHOLD = H.HOUSEHOLD
                 group by    H.STATE,
                             H.ZIP)
  to          individuals_summary.dat
  with        DELETE;

Additional Resources

See also:

 
Back to top
integration/rdbms/creation.txt ยท Last modified: 2016/06/28 22:38 (external edit)