Postgresql user guide for Apache Doris ODBC appearance

Posted by nic0las on Tue, 04 Jan 2022 03:47:59 +0100

Today is the first day of the Gregorian new year in 2022. I wish you a happy New Year's day. This is also the first article in 2022. Later, I will bring you more articles about Doris. At the same time, I hope Doris will take off in 2022, graduate smoothly from Apache incubator into a top-level project, and bring you a faster, more stable and more ecological MPP OLAP analytical database product.

The master plan of Apache Doris community in 2022 includes functions, documents, community construction and other aspects that need to be carried out or have been carried out, but need to be continuously optimized. We look forward to more small partners participating in the discussion. I also hope to pay more attention to Doris and add Star to Doris

Apache Doris 2022 Roadmap

The user guide is tested for Ubuntu environment. Centos environment can be used for reference, but it is not guaranteed to succeed.

1. Software environment

  1. Operating system: ubuntu 18.04
  2. Apache Doris : 0.15
  3. Postgresql database: PostgreSQL 12.9
  4. UnixODBC: 2.3.4
  5. Mysql Connector ODBC : 5.3.13,8.0.11,8.0.26

2. Install ODBC driver

First, we install the UNIX ODBC driver. Here we directly give the download address and installation command of the driver

sudo wget 
tar -xvzf unixODBC-2.3.4.tar.gz 
cd unixODBC-2.3.4/
sudo ./configure --prefix=/usr/local/unixODBC-2.3.7 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etc
sudo make install

After successful installation, the header files required by unixODBC are installed in / usr/inlucde, the compiled library files are installed in / usr/lib, the executable files related to unixODBC are installed in / usr/bin, and the configuration files are placed in / etc.

Verify that the installation was successful

# odbcinst -j
unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8

3. Install Postgresql database

Ubuntu's default repository contains Postgres packages, so you can install them using apt.

Before installation, update the local software package with apt, and then install the Postgres package and a - managed package with additional utilities and functions:

$ sudo apt update
$ sudo apt install postgresql postgresql-contrib

Now that the software is installed, we can understand how it works and how it differs from similar database management systems you may use.

3.1 using PostgreSQL roles and databases

By default, Postgres uses a concept called "roles" to handle authentication and authorization. In some ways, these are similar to regular Unix style accounts, but Postgres does not distinguish between users and groups, but prefers the more flexible term "roles".

After installation, Postgres is set to use ident authentication, which means that it associates postgres roles with a matching Unix / Linux system account. If a role exists in Postgres, a Unix / Linux user name with the same name can log in as the role.

The installation process creates a user account named Postgres, which is associated with the default Postgresroles. To use Postgres, you can sign in to this account.

There are several ways to access Postgres using this account.

3.2 switch to postgres account

Enter the following to switch to the postgres account on the server:

$ sudo -i -u postgres

You can now access Postgres tips immediately by entering the following:

$ psql

This brings you to the PostgreSQL prompt, where you can immediately interact with the database management system.

Exit the PostgreSQL prompt by entering the following command:

postgres=# \q

This will take you back to the postgresLinux command prompt.

3.3 accessing Postgres without switching accounts

You can also have the postgres account run the commands you want with sudo.

For example, in the last example, you are instructed to enter the Postgres prompt by first switching to the Postgres user and then running psql to open the Postgres prompt. You can do this step by running a single command as a Postgres user through psql sudo, as follows:

$ sudo -u postgres psql

This will log in directly to Postgres without an intermediate bash shell.

Similarly, you can exit an interactive Postgres session by entering the following:

postgres=# \q

Many use cases require multiple Postgresroles. Read on to learn how to configure these

3.4 creating users, databases and tables

Log in to postgresql using the default user to create users, create databases, and complete authorization

$ sudo -u postgres psql

Create a new database user, such as dbuser:

postgres=# CREATE USER dbuser WITH PASSWORD 'zhangfeng';

be careful:

The statement should end with a semicolon. The password should be enclosed in single quotes. To create a user database, you can also log in and create a database through the user you created, such as demo:

postgres=# CREATE DATABASE demo OWNER dbuser;

Give all permissions to the demo database to dbuser:


Exit psql using the command \ q:

postgres=# \q

Create an ordinary Linux user with the same name as the newly created database user, such as dbuser:

$ sudo adduser dbuser
$ sudo passwd dbuser

Connect to the database as dbuser exampledb:

$ su - dbuser

Last login: Wed Mar 1 11:52:07 CST 2017 on pts/

Log in to psql with the user we created (dbuser)

# sudo -u dbuser psql -U dbuser -d demo
could not change directory to "/root": Permission denied
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

demo=> \d
                    List of relations
 Schema |           Name            |   Type   |  Owner
 public | playground                | table    | postgres
 public | playground_1              | table    | dbuser
 public | playground_1_equip_id_seq | sequence | dbuser
 public | playground_equip_id_seq   | sequence | postgres
(4 rows)

Creating tables and inserting data

CREATE TABLE playground_test_odbc (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) ,
    install_date date

Sample data

INSERT INTO playground_test_odbc (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT INTO playground_test_odbc (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

results of enforcement

demo=> CREATE TABLE playground_test_odbc (
demo(>     equip_id serial PRIMARY KEY,
demo(>     type varchar (50) NOT NULL,
demo(>     color varchar (25) NOT NULL,
demo(>     location varchar(25) ,
demo(>     install_date date
demo(> );
demo=> INSERT INTO playground_test_odbc (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
demo=> INSERT INTO playground_test_odbc (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

4. Install the Postgresql ODBC driver

Here we download the driver relative to the data version

Postgresql ODBC driver download address:

tar zxvf psqlodbc-12.02.0000.tar.gz
cd psqlodbc-12.02.0000
./configure --without-libpq   (Note: This machine is not installed postgresql,So use without-libpq option) 
make install

If the following error occurs during compilation

configure: error: libpq library version >= 9.2 is required

This is because the package of libpq is missing. You need to install it and execute the following command

apt-get install libpq-dev

The installation is successful. The default driver is placed in / usr / local / lib / psqlodbc W So down

5. Verify whether the ODBC driver is successful

5.1 configuring and registering Postgresql ODBC drivers

Edit / etc / ODBC Inst Ini, add the following

Description = ODBC for PostgreSQL
Driver = /usr/local/lib/
Driver64 = /usr/local/lib/
Setup = /usr/lib/    ##Note that this is the so file path of UNIX ODBC installed in Section 2
Setup64 = /usr/lib/
FileUsage = 1

5.2 configuring PG data source

Edit / etc / ODBC ini

Add the following

Driver = PostgreSQL    ###The name here is ODBC Inst The configuration name in ini is consistent
Description = Postgres DSN
Servername = localhost
Database = demo
Username = dbuser
Password = zhangfeng
Port = 5432
ReadOnly = No

Others are your Postgresql address and the user, password, database and port just created

5.3 is the verification successful

isql -v PostgresDB dbuser zhangfeng
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |

Note that PostgresDB here is our ODBC The name defined in. Ini. ODBC is displayed here

6.Apache Doris PG appearance verification

6.1 modify configuration

Modify the BE node conf / ODBC Inst INI file, add / etc / ODBC Inst Ini and delete the original PostgreSQL configuration

Description = ODBC for PostgreSQL
Driver = /usr/local/lib/
Driver64 = /usr/local/lib/
Setup = /usr/lib/
Setup64 = /usr/lib/
FileUsage = 1

6.2 verification

Create PG ODBC Resource

"host" = "localhost",
 "port" = "5432",
 "user" = "dbuser",
 "password" = "zhangfeng",
 "database" = "demo",
 "table" = "playground_test_odbc",
 "driver" = "PostgreSQL",  
 "odbc_type" = "postgresql",
 "type" = "odbc_catalog"

Create ODBC appearance

 CREATE EXTERNAL TABLE `playground_odbc_12` (
    equip_id int NOT NULL,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) ,
    install_date date
"odbc_catalog_resource" = "pg_12", 
"database" = "demo",
"table" = "playground_test_odbc"

Execute the query under Doris:

ysql> show tables;
| Tables_in_demo     |
| playground_odbc_12 |
| test_odbc_5        |
| test_odbc_8        |
| test_odbc_8_0_26   |
| test_odbc_mysql    |
| test_odbc_mysql_8  |
6 rows in set (0.00 sec)

mysql> select * from playground_odbc_12;
| equip_id | type  | color  | location  | install_date |
|        1 | slide | blue   | south     | 2017-04-28   |
|        2 | swing | yellow | northwest | 2018-08-16   |
2 rows in set (0.01 sec)

OK, everything is normal. Compared with the ODBC driver of Mysql PG, it is simpler. As long as your PG version corresponds to the ODBC driver version, there is no problem.

Topics: PostgreSQL Ubuntu Doris