ApsaraDB for HBase:Functions

cast

cast converts a value from one native data type to another. Conversions follow Java semantics.

Supported conversions

Example

Combine cast with an aggregate function to compute the average of an integer column as a double:

CREATE TABLE persioninfo (c1 int PRIMARY KEY, c2 timeuuid);
SELECT avg(cast(c1 as double)) FROM persioninfo;

now

now takes no parameters. Each call generates a new, unique timeuuid value on the coordinator node.

Using now in a WHERE clause always returns no results, because each value returned by now is guaranteed to be unique.

Example

SELECT * FROM persioninfo WHERE c2 = now()

maxTimeuuid and minTimeuuid

maxTimeuuid returns the largest timeuuid for a given timestamp. minTimeuuid returns the smallest timeuuid. Both accept a timestamp or a date string.

Example

Retrieve rows where c2 falls within a time range:

SELECT * FROM persioninfo WHERE c2 > maxTimeuuid('2013-01-01 00:05+0000')
    AND c2 < minTimeuuid('2013-02-02 10:00+0000') ALLOW FILTERING;

Datetime functions

Datetime functions return the current date or time at the moment of the call.

Time conversion functions

Time conversion functions convert timeuuid, timestamp, or date values into other native data types.

Blob conversion functions

Blob conversion functions convert between native data types and binary blobs.

• <type>AsBlob — accepts a value of the specified native type and returns it as a blob. For example, bigintAsBlob(3) returns 0x0000000000000003.

• blobAs<type> — accepts only 64-bit binary data and converts it to a bigint value. For example, blobAsBigint(0x0000000000000003) returns 3.

Example

CREATE TABLE persioninfo (c1 text PRIMARY KEY, c2 bigint);
INSERT INTO persioninfo (c1, c2) VALUES ('11', blobAsBigint(0x0000000000000003));

count

count counts either the number of non-empty values in a column or the total number of returned rows.

Examples

Count all returned rows:

SELECT COUNT(*) FROM persioninfo;
SELECT COUNT(1) FROM persioninfo;

Count non-empty values in a specific column:

SELECT COUNT(c2) FROM persioninfo;

min and max

min returns the minimum value in a column. max returns the maximum value.

Example

CREATE TABLE persioninfo (c1 text PRIMARY KEY, c2 int);
INSERT INTO persioninfo (c1, c2) VALUES ('k1', 3);
INSERT INTO persioninfo (c1, c2) VALUES ('k2', 4);
SELECT min(c2), max(c2) FROM persioninfo;

sum

sum adds all values in a column.

Example

SELECT sum(c2) FROM persioninfo;

avg

avg calculates the mean of all values in a column.

Example

SELECT avg(c2) FROM persioninfo;