Functions

String functions:

All of the following functions can be used in SELECT or WHERE clause

  1. strtok: String Tokenizer breaks the string into tokens based on a separator character and picks the token specified.

    2. Syntax: strtok(<field|function>,<token>,<position>, <strict true|false>)

    For instance, assume we have a path ‘Apparel/Mens/Shirts/formal.html’. strtok(path, ‘/’, 2) will return ‘Mens’

    The option strict is used to indicate if the last token should be used; if the total number of tokens is less than required tokens. If the value is false, the last token is returned.

    Data: 'Apparel/Mens/Shirts/formal.html'

    Example: strtok(path, '/',10,false) will return 'formal.html'

    strtok(path, '/',10,true) will return empty string ''

    This can be used in the SELECT clause as well as the WHERE clause.

    Example: SELECT strtok(path, '/',3), count(*)

    SELECT path, count(*) WHERE strtok(path,'/',2) = 'Mens'

  2. tolowercase: Converts the string to lower case.

    3. Syntax: tolowercase(<fieldName>)

    Examples: SELECT tolowercase(account), out

    SELECT description, out WHERE tolowercase(account) = food

  3. touppercase: Converts the string to upper case.

    4. Syntax: touppercase(<fieldName>)

    Examples: SELECT touppercase(account), out

    SELECT description, out WHERE touppercase(account) = FOOD


Date functions:

  1. day: This is the default function for all date values. It returns the date in ‘dd-MMM-yy’ format.

    2. Syntax: day(<dateField>, <outputFormat>)

    Examples: day(date), day(date, 'MMM-dd')

    SELECT day(date, 'MMM-dd'), count(*)

    When using this function in the where clause, you can enter the value in either of the formats (”MM-dd-yyyy”, “MMM-dd-yyyy”,”dd-MMM-yyyy”, “yyyy-MM-dd”)

    Example: SELECT description, out WHERE date = 'Feb-02-2007'

  2. hour: Returns the hour of day

    Syntax: hour(<dateField>)

    Examples: SELECT hour(date), count(*)

    SELECT path, count(*) WHERE hour(date) = 15;

  3. dayofweek: Returns the day of the week (Sunday, Monday, …)

    4. Syntax: dayofweek(<dateField>)

    Examples: SELECT dayofweek(), count(*)

    SELECT path, count(*) WHERE dayofweek(date) = Wednesday

  4. weekofmonth: Returns the week of the month (W1, W2, …)

    5. Syntax: weekofmonth(<dateField>)

    Examples: SELECT weekofmonth(date), count(*)

    SELECT path, count(*) WHERE weekofmonth(date) = W1

  5. weekofyear: Returns the week of the year (W1, W2, …)

    6. Syntax: weekofyear(<dateField>)

    Examples: SELECT weekofyear(date), count(*)

    SELECT path, count(*) WHERE weekofyear(date) = W18

  6. month: Returns the month (January, February, …)

    7. Syntax: month(<dateField>)

    Examples: SELECT month(date), count(*)

    SELECT path, count(*) WHERE month(date) = January

  7. dayofmonth: Returns the day of month (1, 2, 3, …)

    8. Syntax: dayofmonth(<dateField>)

    Examples: SELECT dayofmonth(date), count(*)

    SELECT path, count(*) WHERE dayofmonth(date) = 15

  8. year: Returns the year

    9. Syntax: year(<dateField>)

    Examples: SELECT year(date), count(*)

    SELECT path, count(*) WHERE year(date) = 2007

  9. todate: This function has been described before for converting string type to date in case of CSV or Delimited files. It can also be used for to give specific values to the date function.

    10. Syntax: todate(<dateValue>,<dateFormat>)

    Example: SELECT path, count(*) WHERE date > todate('jan-05-07','MMM-dd-yy')

Misc Functions

  1. count: This function returns the number of rows in a query.

    2. Syntax: count(expression)

    Examples SELECT path, count(*)

    SELECT count(host)

    SELECT host, count(path)

  2. hostname: for a column containing IP address, this function returns the host name or the same IP if it is not able to resolve.

    3. Syntax: hostname(<fieldName>)

    Examples: SELECT hostname(ip), count(*)

    SELECT hostname(ip), bytes WHERE hostname(ip) like '%.us.comp%'

  3. urldecode: Decodes a string from the application/x-www-form-urlencoded MIME format.

    4. Syntax: urldecode(<fieldName>)

    Examples: SELECT urldecode(referer), count(*)

    SELECT path, count(*) WHERE urldecode(referrer) like '%key word%'

  4. urlattribute: Extract the given key’s value from the query string of the URL.

    5. Syntax: urlattribute(<fieldName>, <attribute>, <decode true|false>)

    Examples: SELECT urlattribute(referer,'q'), count(*) WHERE referer like '%.google.com/%'

    SELECT path, count(*) WHERE urldecode(referrer) like '%key word%'