Lytics Query API

Schema management api to add/edit queries and user-fields. Lytics Query Language ============================= The Lytics Query Language is used to define the transformation of uploaded records, and event data into user Profiles. It transforms row-level event data into Document-oriented User info. This Query langage is similar to the HIVE or SQL query langauges, however departs from these in order to offer more of a *Rich Document* (json user profile) construction. **Query example** ``` # Build a user from web data SELECT name -- Simple field, by default = string , age KIND INT -- cast field as int , last_visit_ts KIND DATE -- cast as date -- Showing the aggregate counter function and aliasing name of output column AS , count(_ref) AS ref_ct -- Valuect makes a map[string]int count of occurences of a key , valuect(`my field`) AS myfield_mapct -- showcase every optional syntax element in column -- meregeop oldest we don't want to over-write this value, keep oldest -- KIND INT normally we don't have to cast as most functions have a specific type , amt AS first_order_amount IF event == "cart checkout" SHORTDESC "Amount of First Order" LONGDESC "Amount of First Order" KIND INT MERGEOP OLDEST -- lets keep around the date at which they signed up (mergeop oldest) , now() AS signedup_date IF event == "signed up" KIND DATE MERGEOP oldest -- maps: map all fields that start with "user." into a fact map , match("user.") AS user_attributes KIND map[string]string -- list of strings , set(event) AS all_events -- Identified By Columns allow merging across streams , email(EmailAddress) AS email , _uid , fbuid FROM default INTO user BY _uid OR email OR fbuid WHERE _bot = "f" OR NOT EXISTS _bot ALIAS web_user; # validate the query curl -s -XPOST "https://api.lytics.io/api/query/_validate" \ -H "Authorization: $LIOKEY" \ -H "Content-Type: text/plain" \ --data-binary @/tmp/tmp.lql | jq '.' # upload the query curl -s -XPOST "https://api.lytics.io/api/query" \ -H "Authorization: $LIOKEY" \ -H "Content-Type: text/plain" \ --data-binary @your_file.lql | jq '.' # look at schema it output: curl -s -H "Authorization: $LIOKEY" \ -XGET "https://api.lytics.io/api/schema/user" | jq '.' ``` **Standard Syntax** ``` Select = "SELECT" COLUMNS FROM INTO BY [WHERE] ALIAS # required from, the stream to operate on for this query FROM = "FROM" Identifier # Required Identified By field, name of column "AS" from Column BY = "BY" Identifier ["OR" Identifier] # Required Alias for giving a query a unqique identifier ALIAS = "ALIAS" Identifier # Optional Where Filter, same as SQL where WHERE = "WHERE" LogicalExpression COLUMNS = COLUMN [, COLUMN] COLUMN = Expression ["AS" Identifier] ["IF" LogicalExpression] ["SHORTDESC" String] ["LONGDESC" String] ["KIND" Kind] ["MERGEOP" MergeOp] LogicalExpression = NOT | Comparison | EXISTS | IN | CONTAINS | LIKE | Function | Expression | "(" LogicalExpression ")" | LogicalExpression OR LogicalExpression | LogicalExpression AND LogicalExpression Expression = Identifier | Function | Literal Function = Identifier "(" Expression [, Expression] ")" NOT = "NOT" LogicalExpression Comparison = Identifier ComparisonOp Literal ComparisonOp = ">" | ">=" | " "apples,oranges"` * `join("apples","oranges","") => "applesoranges"` - **len** Length (of array, string) - **oneof** Choose value from the first field that has a non nil value. * `oneof(fielda,fieldb,fieldc)` - **replace** - Replace a matching part of a string with an empty string. Converts to string first. * `replace(url,"/search/apachesolr_search/")` - Removes `/search/apachesolr_search/` from URL (in this case, leaving the search term - **split** Breaks a variable into smaller fragments given a specific delimiter * `split(cc,",")` - Splits the variable `cc` at each comma it contains - **strip(field)** Strips leading and trailing whitespace (spaces, tabs, newline, carriage-return) from string, or arrays of strings. - **string.lowercase** Convert strings to lower case - **string.uppercase** Convert strings to upper case - **string.titlecase** Convert strings to title case - **contains** Does this value contain this string? Is a sub-string match, not full match (eq) * `IF contains(total_price, "$")` - Check to see if `total_price` has a `$` in it * `IF not(contains(subscriber_key,"-")) AND not(contains(subscriber_key,"@"))` check to make sure `-` or `@` is not in it. - **hasprefix** Does this value start with this string? * `hasprefix(event, "created")` - Check to see if `event` starts with "created" - **hassuffix** Does this value start with this string? * `hassuffix(subscriber_key, "user")` - Check to see if `subscriber_key` ends with "user" **Hash & Encoding Functions** - **hash.sip** `hash.sip(email)` Hash the given value using sip hash to integer output. - **hash.md5** `hash.md5(email)` Hash the given value using md5 - **hash.sha1** `hash.sha1(email)` Hash the given value using sha1 - **hash.sha256** `hash.sha256(email)` Hash the given value using sha256 - **hash.sha512** `hash.sha512(email)` Hash the given value using sha512 - **encoding.b64encode(field)** base64 encode. - **encoding.b64decode(field)** base64 decode. **Cast & Convert** - **toint** Converts strings to integers. Useful for converting a string to a number before applying a number-based expression. * `toint(order_total)` - Converts `order_total` to an int * `set(toint(split(cc,",")))` - Takes the field `cc` and splits it at commas, and converts the results to integers. Then adds them to a set. - **tonumber** Convert to Number - **todate** Converts strings to dates, see full doc in Date/Time section below. - **tobool(field)** Cast to Boolean. **Map & Set/Array Functions** - **filter** Filter out Values that match specified list of match filter criteria * `filter(split("apples,oranges",","),"ora*") => ["apples"]` - **len** Length (of array, string) - **map** Type: Map `map(key1, todate(date_field))` * `map(key1, todate(date_field)) KIND map[string]time ` By default the `map` is generic map, cast to map[string]time with - **match** Type: Map (generic map, use KIND to cast) Match a key, and then keep a map of key/values with the match value removed * `, match("topic_") AS global KIND map[string]number` - **mapkeys** Type: Map input, []string{} output. Given a map, return a list of string of each of the keys. - **mapvalues** Type: Map input, []string{} output. Given a map, return a list of string values of each of the values. - **mapinvert** Type: Map input, MapString output. Given a map, return a map[string]string inverting keys/values. - **array.index** Cherry pick a single item out of an array: * `array.index(split("apples,oranges,peaches",","),1) => ["oranges"]` - **array.slice** Slice an array of items selecting some sub-set of them. * `array.slice(split("apples,oranges,peaches,pineapple",","),2) => ["peaches","pineapple"]` * `array.slice(split("apples,oranges,peaches,pineapple",","),1,3) => ["oranges","peaches"]` **Url/Http & Email Functions** - **email** Extract email address from "`Bob `" format - **emailname** Extract *Bob* from "`Bob `" or `email@gmail.com` - **emaildomain** Extract *gmail.com* from "`Bob `" or `email@gmail.com` - **domain** Extract domain from url - **host** Extract host from url - **path** Extract the url path from url (no query string or domain), must be valid url parserable string. - **qs** Extract the querystring parameter from url `qs(urlfield, "nameOfParam")` * `qs(url, "mc_eid")` - Extracts the MailChimp user ID * `set(qs(url, "video_id")` - Creates a set of `video_id` * `qs(tolower(url), "riid")` - Converts the complete URL to lowercase before attempting to match * `email(oneof(email, qs(url, "email")))` - Attempts to get the email address from the URL and from the regular fields, chooses whichever is populated and treats it like an email field - **urldecode** Perform URL decode on a field. `urldecode(field)` * If `field` contains "my%20value", `urldecode(field)` will return "my value" - **urlminusqs** The url minus the querystring portion - **useragent** Extract info from user-agent string. Below examples based on `Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11` * `useragent(user_agent, "bot")` - Extracts True/False is this a bot? * `useragent(user_agent, "mobile")` - Extracts True/False is this mobile? * `useragent(user_agent, "mozilla")` - Extracts "5.0" * `useragent(user_agent, "platform")` - Extracts "X11" * `useragent(user_agent, "os")` - Extracts "Linux x86_64" * `useragent(user_agent, "engine")` - Extracts "Linux x86_64" * `useragent(user_agent, "engine_version")` - Extracts "AppleWebKit" * `useragent(user_agent, "browser")` - Extracts "Chrome" * `useragent(user_agent, "browser_version")` - Extracts "23.0.1271.97" - **useragent.map(field)** Extract map of all of above. **Date & Time Functions** Our core date parser recognizes about 50 date formats, so in general these will operate on _any_ format. If you are using EU dates, you will need to specify the parser format. - **dayofweek** Type: Integer. 0-6 integer of day of week. * Examples: `dayofweek() => 4` OR `dayofweek(mydatefield)` - **epochms** Type: Integer. Unix MS of the date stamp on the current message being processed - **extract** Can be used to extract parts of date and time. Example usage on the [strftime](http://strftime.org/) site * `extract(reg_date, "%B")` Returns name of month * `extract(reg_date, "%d")` Returns day of month - **hourofday** Type: Integer. Hour of day (in 24 hour utc time). `hourofday()` OR `hourofday(field)` - **hourofweek** 0-167 integer for hour of week - **mm** Type: Integer. 0-11 month (alias for monthofyear) `mm()` => current month, 6 for june, `mm(my_date_field)` - **monthofyear** Type: Integer Output the 0-11 month value - **now** Type: Date The current message/event times. - **seconds** Type: Integer. Seconds, extracts things like `seconds("00:30") => 30` and `seconds("10:30") => 630` - **todate** Converts strings to dates. * Datemath: `todate("now-3m")` Date math relative to message timestamp. * Parser: `todate("02/01/2006")` More than 30 formats supported. [Date Parser](https://github.com/araddon/dateparse) * Examples with 2 arguments: `todate("02/01/2006","07/04/2014")` use [golang's time package](http://golang.org/pkg/time/) formatting * `todate("02/01/2006","07/04/2014")` Reformats the date `07/04/2014` from US formatting to UK formatting, with the resulting output being `04/07/2014` * `todate("02/01/2006",date_field_name)` Outputs `date_field_name` as European format (where `01` is a placeholder for month, `02` is a placeholder for day, and `2006` is a placeholder for year) - **todatein** Converts strings to dates, if no location info is provided in date string such as "2017-09-30 17:00:00" this will allow you to apply a timezone. We still convert back to UTC for storage. - **totimeset** Type time slice/array. Takes in times and converts strings to times similar to todate without the formatting parameter. - **totimestamp** Convert to Integer Unix Seconds (UTC). - **yy** Type: int Date conversion to YY format, so May 1 2014 is expressed as 14. yy(dob), or yy() for record time stamp - **yymm** String The YYMM date format, so May 1 2014 is expressed as 1405. yy(dob), or yy() for record time stamp - **timebucket** Creates a tabulation of timestamps which can be used to segment based on timewindows. See [Segments Examples](#segment) for more information. `timebucket(now())` for collect time, or `timebucket(todate(field))` to bucket on the value of a field KINDS (aka Data Types) ----------------------------------- Allows explicitly setting data type. Often this os optional as it is inferred from functional expression. - *int* 64 bit signed integer - *number* 64 bit signed Float value - *bool* Boolean - *date* Date-Time - *string* string - *[]time* Array of times - *[]string* Array of strings - *ts[]string* Time ordered Unique set of strings (useful for keeping track of order in which they performed set of unique events) - *map[string]int* Map of key/integers - *map[string]number* - *map[string]string* - *map[string]time* Merge Operations --------------------- *MERGEOP* Allow Merge behavior's to determine if given new data we want the new field, or keep the previous. * `, my_date KIND DATE MERGEOP oldest` -- Holds the first value seen for my_date * `, old_score KIND INT MERGEOP oldest` -- Holds the oldest value passed in to the field * `set(lists) AS lists KIND []string MERGEOP latest` -- only store latest set (all previous values of set discarded)