To use Tencent Cloud TCHouse-P, you need to perform the following operations:
Creating Cluster
Before creating a Tencent Cloud TCHouse-P cluster, you need to specify the data volume, region where the data resides, and network environment to access the cluster. Currently, only VPCs are supported. Therefore, you need to create a VPC and subnet for cluster access before creating a cluster.
Connecting to Database
After the cluster is created, purchase a CVM instance in the previously configured subnet for access. Then, connect to the database via the psql client in the CVM instance. If you haven't installed the client, install with the following command.
yum install -y postgresql.x86_64
Tencent Cloud TCHouse-P is fully compatible with PostgreSQL 8.3.23 protocols. The basic syntax for connecting to the database with psql is as follows:
psql -h 10.0.0.3 -p 5436 -d postgres -U testuser
Here, postgres is the default database of Tencent Cloud TCHouse-P, testuser is the admin account that you need to enter when creating the database, 5436 is the default port number of the database, and 10.0.0.3 is the VIP returned after database creation (which can be queried in the console).
Importing Data
1. Use INSERT to import the data.
1. You can write the data directly to Tencent Cloud TCHouse-P through the INSERT statement, which is suitable for scenarios with small data volumes.
Use the client tool psql to connect to Tencent Cloud TCHouse-P and write the data through the standard INSERT syntax.
Write the data to Tencent Cloud TCHouse-P through the writer of the PostgreSQL JDBC driver.
2. Use the \\COPY command to import the data.
You can use the \\COPY command to import files to Tencent Cloud TCHouse-P from the server where the client resides, as described in the \\COPY syntax of PostgreSQL.
3. Import the data from COS external tables.
The syntax of COS external tables is as described in COS Data. After creating a readable COS external table, you can use the following syntax to import the data from the table to an internal table with the same structure. INSERT INTO cos_local_tbl SELECT * FROM cos_tbl
4. For more information on importing data from a public cloud or other environments, see Using External Table. Analyzing Data
The Tencent Cloud TCHouse-P syntax is fully compatible with Greenplum Database 5.x. You can use its syntax as a reference for data analysis.
Prerequisites
1. The database is connected under the admin user or another user created by the admin.
2. The corresponding database and database table, such as testdb and testtable, have been created.
3. The data has been inserted into the database and table as instructed in Inserting Data. Simple SELECT statement
SELECT col1,col2,col3 FROM testtable WHERE col1 = val1 AND col2 = val2;
Use the above statement to get records in the testtable table where the value of col1 is val1 and that of col2 is val2.