Use a Pump to Move Data from s-Server into a Database.
Now that you've learned the basics of using a pump, we're going to use a pump to move data from s-Server to an external database.
Because s-Server does not store data persistently--that is, after it has stopped flowing--you may want to use an external database to archive data.
Using an interface called SQL/MED, you can access tables in foreign databases as if they were present in s-Server. These are called foreign tables, and you create them just like any other stream or table. To tell s-Server how to connect to the external database, you use a server object. In tutorial #2, we created one of these to pull data into s-Server from a file. This time, we create a server object with the URL, user name/password, and other information needed to connect to a Microsoft SQLserver, PostgreSQL, MySQL, Oracle, Terradata, or other database.
The following steps describe how to set up a connection to a database, and then create a pump to move data from s-Server into the database.
Here, we create a server object with credentials for the external database: url, user name, password, driver name, and schema. s-Server can only communicate with one database schema for each server object.
Again, in order to move data into an external database, we need to set up two objects in s-Server:
1.a server object with connection information for the database.
2.a foreign table that maps to a table in the database.
Setting Up a Server Object
To create a server object that contains connection information for the database. The particular credentials here will depend on your database. See your database's administrator if you do not know these credentials.
The code below creates a serve object for a PostgreSQL database running on a local machine.
CREATE OR REPLACE SERVER "PostgreSQL_DB_1"
FOREIGN DATA WRAPPER "SYS_JDBC"
Now we're going to set up a foreign table in s-Server, which references this server object. The foreign table matches a table in the database whose connection we just defined. A foreign table essentially provides an "interface" to a table in an external database, allowing you to move data in and out of this table from within s-Server.
To set up a foreign table in s-Server, use code along the following lines. As with streams and pumps, we create tables within schemas. The code below first sets the same schema buses that we've been using all along.
We do need to make sure that if we declare columns for the foreign table, that these columns have compatible types. For example, if you have a column defined as type DOUBLE in s-Server, it needs to correspond to a column in the foreign database table with type DOUBLE.
s-Server also give you options to manage how frequently you update the database:
•The first option, TRANSACTION_ROWTIME_LIMIT, lets you limit update frequency in terms of time, telling s-Server "wait this number of milliseconds before updating." Here, we've set this value to 1000, or one second. This means that the database table will be updated every second.
•The second option, TRANSACTION_ROW_LIMIT, lets you limit update frequency in terms of the amount of data to be updated. The default value is 65536/maxSize, where maxSize is the maximum row size of the input stream. Here, we've set this value to 0. This means that the database will be updated each second (per TRANSACTION_ROWTIME_LIMIT) without regards to the amount of data being updated.
SET SCHEMA '"buses"';
CREATE FOREIGN TABLE "postgres_archive"
--these are column names for the foreign table as it exists in s-Server
--this is the table name in the postgres database.
--Amount of data to wait for before committing.
--Amount of time to wait between commits.
Now that we've created the foreign table in s-Server, we can easily move data into the external base, simply by pumping it into this foreign table.
Here, we're going to create a pump to move data from s-Server into the external database. This pump will SELECT on the "buses" stream with which we've been working and INSERT into the foreign table. The effect will be to copy data from the s-Server stream into the external database table.
CREATE PUMP "buses"."postgres-pump" AS
INSERT INTO "buses"."postgres_archive"
("id", "reported_at", "shift_no", "trip_no")
SELECT STREAM "id", "reported_at", "shift_no", "trip_no"
Once again, pumps are created as stopped by default. To get data moving, we just need to execute an ALTER PUMP statement.
ALTER PUMP "buses"."postgres-pump" START;
At this point, your external database table should have data added to the "id", "reported_at", "shift_no", and "trip_no" columns.
Once you get data into s-Server, pumps are a great way to move data around. In the next section, we'll learn about the importance of VIEWS in s-Server.