ODBC Input Build Tags

A Spectre Build script is defined using a build tag with optional tags between open and close { } braces. This topic describes the build tags that can be used within the odbc-input code block. The tags are listed in alphabetical order in the following table.

ODBC Input Build Tags

Tags Examples Notes
build

build {

...

}

Defines the block for this build description.

allow-datetime

(Deprecated)

odbc-input {

dsn "bugzilla"

query "select \"lastdiffed\" from \"bugs\" LIMIT 10"

allow-datetime

}

This tag is deprecated. Previous usage allowed a timestamp column in the ODBC source to be recognized as a datetime column. Without this tag, an SQL datetime field was treated as a date type, discarding the time portion of the field.

Datetime columns are now recognized without using this attribute.

bind

build {

take-parameter "INTNUMB" default="11167800"

odbc-input limit-rows=100 {

dsn "star-prod"

query """

SELECT INTNUMB ,SEX ,BIRTHDATE

FROM AGEDEMO WHERE INTNUMB = ? """ {

bind "$(INTNUMB)"

}

}

output "/temp/import_star_agedemo.cbase"

}

Binds the parameter value to the variable in the SQL query.

If the driver does not support describing parameters, Spectre ODBC reader hands off the parameters as strings (similar to Integrator).

TIP: If you know the SQL where clause is expecting a numeric, use Spectre's parse_integer() function to change each string data-type parameter value into an integer value . For example, change bind "$(INTNUMB)" to bind `parse_integer(param("INITNUMB"))`.

calendar

column-override "Quarter" type="period" calendar="standard" period="year-quarter"

column-override "Fiscal Month" type="period" calendar="offset August" period="year-month"

Assigns a calendar and period type to the column. Valid calendars are:

  • standard—Gregorian calendar; period type options are year, year-half, year-quarter, year-tertile, and year-month.
  • offset <month>—Fiscal year similar to the standard calendar except starting on a different month; period type options are the same as standard calendars.
  • iso 8601—ISO week-numbering system; period type options are year and year-week.
  • custom—Custom calendar created from a text file with a contiguous range of dates; required columns are date and year, optional columns are half, quarter, tertile, month, and week.

See General Build Tags for a different use of calendar as a Build tag.

calendar

(Deprecated)

column-override "Quarter" type="period" calendar="gregorian month" format="YYYY/MM" input-format="YYYY/M" Supports a 'period' type column. Valid calendars are:
  • gregorian month—Gregorian year-month
  • gregorian quarter—Gregorian year-quarter
  • iso8601 week—ISO-8601 year-week
  • gregorian+<N> month—Offset Gregorian year-month for fiscal calendars. Specifying "gregorian+6 month" means "add six months to the real year-mo to get the fiscal year-mo", which is like saying "The fiscal year starts in July".
  • <custom calendar file>—For more tailored fiscal calendars
  • See Spectre Calendars.
column

column "Order Type" type="string"

column "Order Unit Price" type="fixed100"

Defines the columns for the cBase. This is the same tag as used by text-input. See column for more information.
column-override

column-override "Order ID" type="string"

column-override "Order Unit Price" type="fixed100"

As of Spectre 7.2, column-override is deprecated. Use column instead.

Specifies the columns for the cBase and overrides the indicated attributes. For example, override the default numeric type to string; specify that the number has 2 decimal places.

NOTE: Use of column-override causes the Spectre build engine to request the given field from the database using a specific type. If the database or its ODBC driver cannot perform that conversion, then an error is generated. Use of column-override does not cause the Spectre build engine to perform any conversions itself.

TIP: The column-override attribute is not intended to be used as a way to do data type casting. For example, in general, column-override does not work with date data in a DB. Either create an SQL query that converts the field, or, once read by the Build, replace the value using an explicit date() function.

connect-file

odbc-input {

connect-file "my_connect_file.connect"

...

}

Specifies a binary file to retrieve credentials. This assumes that you previously generated a connect file using Integrator. For example:

echo "DSN=my-dsn;UID=my-user;PWD=my-password" | integ -save_connect_file my_connect_file.connect

See Options for ODBC Login Credentials.

connect-string

odbc-input {

connect-string "DSN=my-dsn;UID=my-user;PWD=my-password" ...

}

Specifies, using one tag, a string with DSN, user, and password.

NOTE: Different ODBC drivers might use other connect string formats.

custom property custom-property "Confidence" "High" Defines custom-properties to associate extra data with a column. The custom-property can be extracted after the build and used in expressions.
dsn

dsn "Northwind"

dsn "Meditech"

Specifies the data source name (DSN) for the ODBC data.
duration

column-override "Visit Duration" type="integer" duration=true

Indicates that the numeric value is meant to be displayed as a time duration. The format is h:mm:ss.

Use with column-override tags in odbc-input blocks.

encoding

odbc-input encoding="utf-8" {

...

}

Defines the encoding used in the input and output files. Encoding types supported include: cp-1252, iso-8859-1, utf-16be, utf-16le, utf-8, and gb18030.

format

column-override "Request Date" type="date" format="YYYY-MM-DD" input-format="YYYY-M-D"

column-override "Price" format="$#,0.00"

column-override "Revenue" type="fixed100" format="$#,#.00"

Formats the "date" or "period" data for the cBase. To store dates like July 4, 2013, use format="MMMM D, YYYY".

Use to format currency.

input-format column-override "Date" type="date" format="MMM D, YYYY" input-format="YYYY-MM-DD" Clarifies the incoming 'date' or 'period' data. The default format is YYYY/MM/DD. Use input-format to define the date format in the source to ensure correct interpretation of date data.
label column-override "Color" type="string" label="favorite" Defines the display label used for a column.
limit-rows limit-rows=100

Directs the build to use a specific number of rows from each input. If multiple inputs are used, the first 100 rows are read from each, ensuring a complete sampling of data values.

TIP: Using a "LIMIT" statement or "TOP X" in your SQL query is the optimal way to limit the number of rows coming from the database.

odbc-input

odbc-input encoding="UTF8" {

dsn "Northwind"

query """

select Orders.OrderID, Orders.OrderDate,

Orders.ShippedDate from Orders

"""

}

odbc-input query-path="/data/inventory.sql" {

...

}

Sets the ODBC input. Note the following:
  • ODBC data source name (DSN) and query are required.
  • ODBC user, password, column-override types, calendar, and format are optional.
  • Zero or more column-overrides, properties, and custom-properties are allowed.
  • A text or variable length field with an unspecified maximum length is capped at 32,767 characters.
  • When run, key stages are reported: opening the DSN, running the query, reading the structure of the result, reading the data of the result, and constructing the output table.
  • The query can be inline or stored in an external file.
password password "mysecret" Sets the password for the user logging into the ODBC data. This tag is optional.
property property "Function" "Managers" Verbose way to set label/format/calendar properties.
Deprecation expected.
query

query """

SELECT Orders.OrderID

Orders.OrderDate,

Customers.CompanyName as CustomerName,

Customers.ContactName as CustomerContact,

Customers.Phone as CustomerPhone

FROM Orders, Customers
WHERE Orders.CustomerId = Customers.CustomerId
"""

Specifies the query into the database. The SQL query string takes multiple lines, so triple quotes are needed to open and close the string.

See also query-path. Use query or query-path, but not both.

query-path

odbc-input query-path="/data//revenue-one-query.sql"

 

odbc-input query-path="/data/queries/$(TYPE)_drg.sql"

Specifies the path to a query that is saved in an external file.

query-timeout

query-timeout 10 Sets an integer number of seconds after which the ODBC query times out.
required-dimension column-override "Product Status" type="string" required-dimension="Status" Sets a column to behave like classic Info Fields. In ProDiver, this field is available as a column that you can add to other columns displayed in a dive.
sort-by

column-override "Symbol" type="string" sort-by="Number"
column-override "Number" type="integer"

 

column-override "Priority Sort" type="integer"
column-override "Priority" type="string" sort-by="Priority Sort"

Sorts the data when processing. When specifying columns, use sort-by to order them based on another column's value.

sort-natural

column-override "ICD-10-code" type="string" sort-natural=false

add "diagnosis" `value("Diagnosis Code")+" -- "+value("Diagnosis Desc")` sort-natural=false

Disables natural string sorting in odbc-input. The default is true—Spectre interprets value of digits as decimal numbers and sorts according to the numeric values in the string.

TIP: This feature only makes sense together with sort-by if the sort-by column is also a string column.

stale-after

cplan {

cbase-input "foo.cbase" {

lookup {

odbc-input {

dsn="some dsn"

query="select a, b from foo"

stale-after 60

}

key "a" "a"

column "b"

}

}

}

Defines a time threshold, in seconds, for a cache entry to turn stale. Use with odbc-input.

The example here will re-run the lookup at most once every minute so that people looking at the result see data at most one minute old.

The stale-after attribute can also be used with tunnel-input.

suggested-dimension column-override "Product Class" type="string" suggested-dimension=false Allows you to promote (true) or demote (false) a column:
  • Set to true to promote a numeric column type to become a diveable dimension and have it appear in the ProDiver console.
  • Set to false to demote a column from classification as a dimension so it does not appear in the ProDiver console.
  • When set to false, the new dimension does not appear in the console (which requires dimcount processing), but it can still be promoted to a dimension for diving—equivalent to the concept of a dynamic dimension for a classic model.
  • Using required-dimension on a column effectively sets suggested-dimension=false.
summary-type column-override "Quantity In Stock" type="integer" summary-type="info"

Sets the column's summary type explicitly. Any summary function that takes a single argument (except for count and percentile) are allowed. See Summary Functions.

Most common are sum, info, any, first, and last. The default for numeric data is sum; use kahan_sum if concerned about precision; use const for a column with only one value. The rest of the functions (for example, min, average, median) are better done as a separate calc (that is, not the default one).

trim

odbc-input trim=false {

...

}

Controls removal of leading and trailing white space before writing strings to the cBase. Defaults to true.
type

column-override "Qty Ordered" type="integer"

column-override "Telephone Number" type="string"

column-override "OrderUnitPrice" type="fixed100"

Specifies the type of data in the column. Valid values are boolean, date, datetime, double, fixed100, integer, period, and string. See Spectre Data Types.

If the type is period, a calendar tag is required.

NOTE: Fields of type bit from SQL Server default to type string in Spectre. You can specify the type as boolean for better performance.

user user "joey" Sets the user to use to log into the ODBC data. This tag is optional.

See also: