MogDB
Ecological Tools
Doc Menu

Before You Start

This section explains how to use databases, including creating databases and tables, inserting data to tables, and querying data in tables.

Prerequisites

MogDB is running properly.

Procedure

  1. Log in as the OS user omm to the primary node of the database.

    If you are not sure which server the primary node of the database is deployed on, see Confirming Connection Information.

  2. Connect to a database.

    gsql -d postgres -p 8000

    If the following information is displayed, the connection has been established:

    gsql ((MogDB 1.1.0 build 5be05d82) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131)
    Non-SSL connection (SSL connection is recommended when requiring high-security)
    Type "help" for help.
        
    postgres=# 

    postgres is the database generated by default after MogDB installation is complete. You can connect to this database to create a database. 8000 is the port number of the database primary node, and you can change it as needed. You can obtain the port number by following the instructions provided in Confirming Connection Information.

    Note:

    • You need to use a client program or tool to connect to the database and to deliver SQL statements
    • gsql is a command-line interface (CLI) tool provided for connecting to a database. For more database connection methods, see Connecting to a Database.
  3. Create a database user.

    Only administrators that are created during MogDB installation can access the initial database by default. You can also create other database users.

    CREATE USER joe WITH PASSWORD "Bigdata@123";

    If the following information is displayed, the user has been created:

    CREATE ROLE

    In this case, you have created a user named joe, and the user password is Bigdata@123.

    Note: For details about how to create users, see Managing Users and Their Permissions.

  4. Create a database.

    CREATE DATABASE db_tpcc OWNER joe;

    If the following information is displayed, the database has been created:

    CREATE DATABASE

    After creating the db_tpcc database, you can run the following command to exit the postgres database and log in to the db_tpcc database as the user you created for more operations. You can also continue using the default postgres database.

    postgres=#  \q
    gsql -d db_tpcc -p 8000 -U joe -W Bigdata@123
    gsql ((MogDB 1.1.0 build 5be05d82) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131)
    Non-SSL connection (SSL connection is recommended when requiring high-security)
    Type "help" for help.
         
    db_tpcc=> 

    Create a schema.

    db_tpcc=> CREATE SCHEMA joe AUTHORIZATION joe;

    If the following information is displayed, the schema has been created:

    CREATE SCHEMA

    Note:

    New databases are created in the pg_default tablespace by default. To specify another tablespace, run the following statement:

    postgres=#  CREATE DATABASE db_tpcc WITH TABLESPACE = hr_local;
    CREATE DATABASE

    hr_local indicates the tablespace name. For details about how to create a tablespace, see Creating and Managing Tablespaces.

  5. Create a table.

    • Create a table named mytable that has only one column. The column name is firstcol and the column type is integer.

      db_tpcc=>  CREATE TABLE mytable (firstcol int);
      CREATE TABLE
    • Run the following command to insert data to the table:

      db_tpcc=> INSERT INTO mytable values (100);

      If the following information is displayed, the data has been inserted:

      INSERT 0 1
    • Run the following command to view data in the table:

      db_tpcc=> SELECT * from mytable;
       firstcol 
      ----------
            100
      (1 row)

    Note:

    • By default, new database objects, such as the mytable table, are created in the $user schema. For more details about schemas, see Creating and Managing Schemas.
    • For more details about how to create a table, see Creating and Managing Tables.
    • In addition to the created tables, a database contains many system catalogs. These system catalogs contain MogDB installation information and information about various queries and processes in MogDB. You can collect information about the database by querying system catalogs. For details, see Querying System Catalogs.

      MogDB supports row and column storage, providing high query performance for interaction analysis in complex scenarios. For details about how to select a storage model, see Planning a Storage Model.