Data Lake Analytics - Deprecated:Create a Redis table and read data from the table

Create a Redis table

CSV

For CSV data, create a table named dla_person_csv in DMS for Data Lake Analytics, as shown in this example:

CREATE EXTERNAL TABLE dla_person_csv (
    id int,
    name varchar,
    age int
) TBLPROPERTIES (
    COLUMN_MAPPING = 'id,0;name,1;age,2',
    TABLE_MAPPING = 'world_',
    format = 'csv'
);

• COLUMN_MAPPING:

  • This parameter specifies the mapping between columns in DLA and CSV data in your Redis instance.

    As column is not an underlying data structure of Redis, the specific mapping method varies with the COLUMN_MAPPING setting.

    In this example, CSV data is parsed into a string array, and columns in DLA are mapped to indexes of the array as follows in COLUMN_MAPPING: The id column maps index 0, the name column maps index 1, and the age column maps index 2. The topic Read data from an ApsaraDB for Redis instance describes the COLUMN_MAPPING effect in detail.

• TABLE_MAPPING: With the value world_ and the prefix hello_ specified when you create the Redis schema, all the data containing the prefix hello_world_ in your Redis instance is retrieved.

  If you do not set TABLE_MAPPING, the table name is used as the prefix by default. In this case, all the tables that contain the prefix hello_dla_person_csv are retrieved.

JSON

For JSON data, create an external table named dla_person_json in DMS for Data Lake Analytics:

CREATE EXTERNAL TABLE dla_person_json (
    id int,
    name varchar,
    age int
) TBLPROPERTIES (
    COLUMN_MAPPING = 'id,age;name,name;age,id',
    TABLE_MAPPING = 'foo_',
    format = 'json'
);

• TABLE_MAPPING: With the value foo_ and the prefix hello_ specified when you create the Redis schema, all the data containing the prefix hello_foo_ in your Redis instance is retrieved.

• COLUMN_MAPPING: indicates the mapping between columns in DLA and field names in the JSON data. In this example, the id column maps the age field, the name column maps the name field, and the age column maps the id field.

Read data from an ApsaraDB for Redis instance

CSV

Read data from the dla_person_csv table:

select * from dla_person_csv;
+-------+------+------+
| name  | id   | age  |
+-------+------+------+
| lily  |   3 |    30 |
| lucy  |   4 |    20 |
| bond  |   2 |    20 |
| james |   1 |    10 |

JSON

Read data from the dla_person_json table:

select * from dla_person_json;
+-------+------+------+
| name  | id   | age  |
+-------+------+------+
| lucy  |   210 |   3 |
| james |   110 |   1 |
| bond  |   210 |   2 |
| lily  |   310 |   3 |

Then, you can use your MySQL client or MySQL CLI tool to connect to DLA and execute standard SQL statements in your Redis database.