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

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> -- ************************************************************\
[...]

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

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

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.

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

Although the tables that are created using the script are replicated by default, they are not persistent. Exit the gfxd session and shut down the two data store members:

gfxd> exit;
$ gfxd server stop -dir=$HOME/server1
$ gfxd server stop -dir=$HOME/server2

After the servers have stopped, restart them and connect to the distributed system:

$ gfxd server start -dir=$HOME/server1 -locators=localhost[10101] -client-port=1528
$ gfxd server start -dir=$HOME/server2 -locators=localhost[10101] -client-port=1529
$ gfxd
gfxd> connect client 'localhost:1527';

Repeat the query from the previous step:

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        
-----------------------------------------------------------------------------------------------

0 rows selected

The data dictionary is persisted by default, so you can repeat the previous queries; all database objects are still present through a cluster restart. However, table data is not preserved unless you specifically enable persistence when you create the tables. More information is provided in the persistence tutorial.