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
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
- Operating system: ubuntu 18.04
- Apache Doris : 0.15
- Postgresql database: PostgreSQL 12.9
- UnixODBC: 2.3.4
- 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 ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.4.tar.gz 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 make 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 FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /root/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW 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:
postgres=# GRANT ALL PRIVILEGES ON DATABASE demo 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 Password: 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(> );
CREATE TABLE
demo=> INSERT INTO playground_test_odbc (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT 0 1
demo=> INSERT INTO playground_test_odbc (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');
INSERT 0 14. Install the Postgresql ODBC driver
Here we download the driver relative to the data version
Postgresql ODBC driver download address: https://www.postgresql.org/ftp/odbc/versions/src/
wget https://ftp.postgresql.org/pub/odbc/versions/src/psqlodbc-12.02.0000.tar.gz 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) ./configure make 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
[PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/local/lib/psqlodbcw.so Driver64 = /usr/local/lib/psqlodbcw.so Setup = /usr/lib/libodbc.so ##Note that this is the so file path of UNIX ODBC installed in Section 2 Setup64 = /usr/lib/libodbc.so FileUsage = 1
5.2 configuring PG data source
Edit / etc / ODBC ini
Add the following
[PostgresDB] 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 | | | +---------------------------------------+ SQL>
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
[PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/local/lib/psqlodbcw.so Driver64 = /usr/local/lib/psqlodbcw.so Setup = /usr/lib/libodbc.so Setup64 = /usr/lib/libodbc.so FileUsage = 1
6.2 verification
Create PG ODBC Resource
CREATE EXTERNAL RESOURCE `pg_12` PROPERTIES ( "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
) ENGINE=ODBC
COMMENT "ODBC"
PROPERTIES (
"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.