Data lake is a popular concept in the big data industry that involves storing files in its native format. With a data lake, you can implement big data association analysis across various heterogeneous data sources without any pre-processes such as ETL and data migration, significantly reducing costs and improving user experience.
Alibaba Cloud has just launched its own data lake analysis service, which is called Data Lake Analytics (DLA).
One of the biggest advantages of using Alibaba Cloud Data Lake Analytics (DLA) is that you can use it to analyze the data in your Table Store instance. We will show you how in this article.
Alibaba Cloud Table Store is a fully managed NoSQL cloud database service that enables storage of a massive amount of structured and semi-structured data. Although conceptually similar, Table Store differs from DLA in the following aspects:
Mapping of concepts such as the database and table
Field mapping
To enable Table Store, perform the following operations:
The following takes our test data as an example to explain the entire process (with the detailed application procedure omitted):
Go to the product page at www.alibabacloud.com/products/data-lake-analytics, and click Activate Now to submit a public Beta test application. Alibaba Cloud will review your application as soon as possible.
MySQL command line:
mysql -hservice.cn-hangzhou.datalakeanalytics.aliyuncs.com -P10000 -u<dla_username> -p<dla_password> -c -A
JDBC URL:
jdbc:mysql://service.cn-hangzhou.datalakeanalytics.aliyuncs.com:10000/
username=<dla_username>
password=<dla_password>
Currently, VPC-related policies have been configured and allow you to directly connect DLA to Table Store. Therefore, you do not need to worry about network connectivity between DLA and Table Store. However, as DLA currently does not support public network access, you must use the private network endpoint of Table Store.
Note: DLA is a multi-tenant scenario. Therefore, a new user cannot see any database table when accessing the DLA console for the first time.
mysql> create database hangzhou_ots_test with dbproperties (
catalog = 'ots',
location = 'https://hz-tpch-1x-vol.cn-hangzhou.ots-internal.aliyuncs.com',
instance = 'hz-tpch-1x-vol'
);
Query OK, 0 rows affected (0.23 sec)
#hangzhou_ots_test ---Pay attention to the database name, which allows only letters, digits, and underlines
#catalog = 'ots', ---Set this parameter to ots to distinguish it from other data sources, such as OSS and RDS
#location = 'https://xxx' ---Set this parameter to the Table Store endpoint, which can be learned from the instance information
#instance = 'hz-tpch-1x-vol' ---Specify the instance name because the endpoint may not contain the instance name. The instance name is mapped to a schema on DLA
mysql> show databases;
+------------------------------+
| Database |
+------------------------------+
| hangzhou_ots_test |
+------------------------------+
1 rows in set (0.22 sec)
mysql> show create database hangzhou_ots_test;
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| hangzhou_ots_test | CREATE DATABASE `hangzhou_ots_test`
WITH DBPROPERTIES (
CATALOG = 'ots',
LOCATION = 'https://hz-tpch-1x-vol.cn-hangzhou.ots-internal.aliyuncs.com',
INSTANCE = 'hz-tpch-1x-vol'
) |
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.31 sec)
mysql> use hangzhou_ots_test;
Database changed
mysql> show tables;
Empty set (0.30 sec)
mysql> CREATE EXTERNAL TABLE `nation` (
`N_NATIONKEY` int not NULL ,
`N_COMMENT` varchar(100) NULL ,
`N_NAME` varchar(100) NULL ,
`N_REGIONKEY` int NULL ,
PRIMARY KEY (`N_NATIONKEY`)
);
Query OK, 0 rows affected (0.36 sec)
## `N_NATIONKEY` int not NULL ---- You must set this parameter to not NULL for a primary key
## PRIMARY KEY (`N_NATIONKEY`) ---- The setting must be the same as the primary key sequence in Table Store, and the name must also be consistent
mysql> show tables;
+------------+
| Table_Name |
+------------+
| nation |
+------------+
1 row in set (0.35 sec)
mysql> show create table nation;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| nation | CREATE EXTERNAL TABLE `nation` (
`n_nationkey` int NULL COMMENT '',
`n_comment` varchar(100) NULL COMMENT '',
`n_name` varchar(100) NULL COMMENT '',
`n_regionkey` int NULL COMMENT '',
PRIMARY KEY (`n_nationkey`)
)
TBLPROPERTIES (COLUMN_MAPPING = 'n_nationkey,N_NATIONKEY; n_comment,N_COMMENT; n_name,N_NAME; n_regionkey,N_REGIONKEY; ')
COMMENT '' |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.30 sec)
mysql> select count(*) from nation;
+-------+
| _col0 |
+-------+
| 25 |
+-------+
1 row in set (1.19 sec)
mysql> select * from nation;
+-------------+--------------------------------------------------------------------------------------------------------------------+----------------+-------------+
| n_nationkey | n_comment | n_name | n_regionkey |
+-------------+--------------------------------------------------------------------------------------------------------------------+----------------+-------------+
| 0 | haggle. carefully final deposits detect slyly agai | ALGERIA | 0 |
| 1 | al foxes promise slyly according to the regular accounts. bold requests alon | ARGENTINA | 1 |
| 2 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special | BRAZIL | 1 |
| 3 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold | CANADA | 1 |
| 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d | EGYPT | 4 |
| 5 | ven packages wake quickly. regu | ETHIOPIA | 0 |
| 6 | refully final requests. regular, ironi | FRANCE | 3 |
| 7 | l platelets. regular accounts x-ray: unusual, regular acco | GERMANY | 3 |
| 8 | ss excuses cajole slyly across the packages. deposits print aroun | INDIA | 2 |
| 9 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull | INDONESIA | 2 |
| 10 | efully alongside of the slyly final dependencies. | IRAN | 4 |
| 11 | nic deposits boost atop the quickly final requests? quickly regula | IRAQ | 4 |
| 12 | ously. final, express gifts cajole a | JAPAN | 2 |
| 13 | ic deposits are blithely about the carefully regular pa | JORDAN | 4 |
| 14 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t | KENYA | 0 |
| 15 | rns. blithely bold courts among the closely regular packages use furiously bold platelets? | MOROCCO | 0 |
| 16 | s. ironic, unusual asymptotes wake blithely r | MOZAMBIQUE | 0 |
| 17 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun | PERU | 1 |
| 18 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos | CHINA | 2 |
| 19 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account | ROMANIA | 3 |
| 20 | ts. silent requests haggle. closely express packages sleep across the blithely | SAUDI ARABIA | 4 |
| 21 | hely enticingly express accounts. even, final | VIETNAM | 2 |
| 22 | requests against the platelets use never according to the quickly regular pint | RUSSIA | 3 |
| 23 | eans boost carefully special requests. accounts are. carefull | UNITED KINGDOM | 3 |
| 24 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be | UNITED STATES | 1 |
+-------------+--------------------------------------------------------------------------------------------------------------------+----------------+-------------+
25 rows in set (1.63 sec)
Automatic Security Upgrades with Unattended-upgrades Package
2,599 posts | 762 followers
FollowAlibaba Clouder - January 20, 2021
Alibaba Clouder - August 7, 2020
Alibaba EMR - May 20, 2022
Alibaba Clouder - November 15, 2018
Alibaba Clouder - October 12, 2018
Alibaba Clouder - November 16, 2018
2,599 posts | 762 followers
FollowA Big Data service that uses Apache Hadoop and Spark to process and analyze data
Learn MoreA fully managed NoSQL cloud database service that enables storage of massive amount of structured and semi-structured data
Learn MoreAn encrypted and secure cloud storage service which stores, processes and accesses massive amounts of data from anywhere in the world
Learn MoreMore Posts by Alibaba Clouder
Raja_KT March 1, 2019 at 8:01 am
Interesting one.... "DLA is case insensitive, whereas Table Store is case sensitive."