Apache Doris创建用户、数据库和表创建、数据查询、表结构变更、join操作、高可用、查询参数设置

原创

已于 2022-08-05 07:20:19 修改 · 1.1w 阅读

标签

#Apache Doris #表创建 #insert和查询 #表结构变更 #join

于 2021-10-25 23:46:40 首次发布

本文档详细介绍了Doris的基本和高级使用，包括如何修改密码、创建用户与授权、创建数据库和表，以及数据查询、表结构变更、内存限制、查询超时时间和JOIN操作。此外，还涵盖了Doris的内存限制调整、查询超时设置以及Broadcast和Shuffle Join的使用。

1. 基本使用指南

1.1 修改密码

doris默认是没有密码
连接端口是fe.conf中的query_port参数值

[root@bigdata005 opt]# 
[root@bigdata005 opt]# mysql -h bigdata001 -P 9030 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.0 Doris version 0.14.0-release-Unknown

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> set password for 'root' = password('Root_123');
Query OK, 0 rows affected (0.04 sec)

mysql>

1.2 创建用户并授权

新创建的用户，是没有任何权限，连读的权限都没有

mysql> 
mysql> create user 'test' identified by 'Test_123';
Query OK, 0 rows affected (0.13 sec)

mysql> 
mysql> create database test_db;
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on test_db to test;
Query OK, 0 rows affected (0.02 sec)

mysql>

1.3 语法帮助命令

mysql>
mysql> help create;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   CREATE CLUSTER
   CREATE DATABASE
   CREATE FILE
   CREATE FUNCTION
   CREATE INDEX
   CREATE MATERIALIZED VIEW
   CREATE ROLE
   CREATE USER
   CREATE VIEW
   ROUTINE LOAD
   SHOW CREATE FUNCTION

mysql> 
mysql> help create database;
Name: 'CREATE DATABASE'
Description:
    该语句用于新建数据库（database）
    语法：
        CREATE DATABASE [IF NOT EXISTS] db_name;
Examples:
    1. 新建数据库 db_test
        CREATE DATABASE db_test;

mysql>

1.4 创建数据库

mysql> 
mysql> create database if not exists test_db;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test_db            |
+--------------------+
2 rows in set (0.01 sec)

mysql> 
mysql> use test_db;
Database changed
mysql>

information_schema是为了兼容mysql，实际数据不准确

1.5 表的创建

mysql> 
mysql> create table table1(
    -> id int default '0',
    -> name varchar(32) default '',
    -> city_code smallint,
    -> pv bigint sum default '0'
    -> )
    -> aggregate key(id, name, city_code)
    -> distributed by hash(id) buckets 10
    -> properties('replication_num' = '3');
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> create table table2(
    -> id int default '0',
    -> name varchar(32) default '',
    -> city_code smallint,
    -> event_day date,
    -> pv bigint sum default '0'
    -> )
    -> aggregate key(id, name, city_code, event_day)
    -> partition by range(event_day)
    -> (
    -> partition p202107 values less than ('2021-08-01'),
    -> partition p202108 values less than ('2021-09-01'),
    -> partition p202109 values less than ('2021-10-01')
    -> 
    -> )
    -> distributed by hash(id) buckets 10
    -> properties('replication_num' = '3');
Query OK, 0 rows affected (0.34 sec)

mysql> 
mysql> desc table1;
+-----------+-------------+------+-------+---------+-------+
| Field     |