Create Replicated Tables and Execute Queries

By default GemFire XD replicates tables to members of the cluster. In this tutorial you create new tables that are replicated the GemFire XD cluster.

Procedure
  1. In the same gfxd session, run the ToursDB_schema.sql script to create the tables associated with the ToursDB sample database:
    gfxd> run 'ToursDB_schema.sql';

    You see DDL output such as:

    CREATE TABLE AIRLINES
       (
          AIRLINE CHAR(2) NOT NULL CONSTRAINT AIRLINES_PK PRIMARY KEY,
          AIRLINE_FULL VARCHAR(24),
          BASIC_RATE DOUBLE PRECISION,
          DISTANCE_DISCOUNT DOUBLE PRECISION,
          BUSINESS_LEVEL_FACTOR DOUBLE PRECISION,
          FIRSTCLASS_LEVEL_FACTOR DOUBLE PRECISION,
          ECONOMY_SEATS INTEGER,
          BUSINESS_SEATS INTEGER,
          FIRSTCLASS_SEATS INTEGER
       );
    0 rows inserted/updated/deleted
    gfxd> -- ************************************************************\
    [...]
  2. Run the loadTables.sql script to populate the tables with data:
    gfxd> run 'loadTables.sql';
    The script output completes with:
    gfxd> insert into FLIGHTAVAILABILITY values ('US1357',2,'2004-04-18',0,0,3);
    1 row inserted/updated/deleted
  3. Enter the following command to show the table names that you created (tables in the APP schema):
    gfxd> show tables in APP;
    TABLE_SCHEM      |TABLE_NAME                    |REMARKS            
    --------------------------------------------------------------------
    APP              |AIRLINES                      |                   
    APP              |CITIES                        |                   
    APP              |COUNTRIES                     |                   
    APP              |FLIGHTAVAILABILITY            |                   
    APP              |FLIGHTS                       |                   
    APP              |FLIGHTS_HISTORY               |                   
    APP              |MAPS                          |                   
    
    7 rows selected
  4. The new tables that you create and the data that you load are replicated on the two GemFire XD servers by default. You can check whether tables are partitioned or replicated by querying information in sys.systables. Use the following query to check the data policy that GemFire XD has assigned to the tables you just created:

    gfxd> select tablename, datapolicy from sys.systables where tableschemaname='APP';
    TABLENAME                                                     |DATAPOLICY     
    ------------------------------------------------------------------------------
    FLIGHTS_HISTORY                                               |REPLICATE      
    MAPS                                                          |REPLICATE      
    FLIGHTAVAILABILITY                                            |REPLICATE      
    FLIGHTS                                                       |REPLICATE      
    CITIES                                                        |REPLICATE      
    COUNTRIES                                                     |REPLICATE      
    AIRLINES                                                      |REPLICATE      
    
    7 rows selected

    The output shows that each of the ToursDB tables that you created are replicated. GemFire XD replicates tables by default if you do not use the PARTITION BY clause in the CREATE TABLE statement.

    Partitioning Tables and Replicating Tables provide more information about creating tables in GemFire XD.

  5. You can also use the sys.members table to determine which members host a particular table:

    gfxd> select id from sys.systables s, sys.members where s.tablename='FLIGHTS';
    ID                                                                             
    -------------------------------------------------------------------------------
    192.168.125.147(39598)<v2>:23162                                               
    192.168.125.147(39486)<v1>:40258                                               
    
    2 rows selected
    In the tutorial, both of the GemFire XD servers store information for each replicated table.
  6. GemFire XD provides query features similar to those available in other data management products. For example, the following command executes a simple query:
    gfxd> SELECT city_name, country, language FROM cities WHERE language LIKE '%ese';
    CITY_NAME               |COUNTRY                   |LANGUAGE        
    --------------------------------------------------------------------
    Rio de Janeiro          |Brazil                    |Portuguese      
    Sao Paulo               |Brazil                    |Portuguese      
    Hong Kong               |China                     |Chinese         
    Shanghai                |China                     |Chinese         
    Osaka                   |Japan                     |Japanese        
    Tokyo                   |Japan                     |Japanese        
    Lisbon                  |Portugal                  |Portuguese      
    
    7 rows selected

    The following query executes a join between tables:

    gfxd> SELECT city_name, countries.country, region, language
    FROM cities, countries
    WHERE cities.country_iso_code = countries.country_iso_code AND language LIKE '%ese';
    CITY_NAME           |COUNTRY            |REGION               |LANGUAGE     
    ----------------------------------------------------------------------------
    Rio de Janeiro      |Brazil             |South America        |Portuguese   
    Sao Paulo           |Brazil             |South America        |Portuguese   
    Hong Kong           |China              |Asia                 |Chinese      
    Shanghai            |China              |Asia                 |Chinese      
    Osaka               |Japan              |Asia                 |Japanese     
    Tokyo               |Japan              |Asia                 |Japanese     
    Lisbon              |Portugal           |Europe               |Portuguese   
    
    7 rows selected