Ruby Cheatsheet
Materialize is PostgreSQL-compatible, which means that Ruby applications can use any existing PostgreSQL client to interact with Materialize as if it were a PostgreSQL database. In this guide, we’ll use the pg
gem to connect to Materialize and issue PostgreSQL commands.
Connect
You connect to Materialize the same way you connect to PostgreSQL with pg
. If you don’t have a pg
gem, you can install it with:
gem install pg
Local Instance
You can connect to a local Materialize instance just as you would connect to a PostgreSQL instance:
require 'pg'
conn = PG.connect(host:"127.0.0.1", port: 6875, user: "materialize")
Materialize Cloud Instance
Download your instance’s certificate files from the Materialize Cloud Connect dialog and specify the path to each file as an environment variable. Replace MY_INSTANCE_ID
in the PG.connect
method with your Materialize Cloud instance ID.
require 'pg'
# Define the Postgres TLS certificates environment variables
ENV['PGSSLCERT'] = 'materialize.crt'
ENV['PGSSLKEY'] = 'materialize.key'
ENV['PGSSLROOTCERT'] = 'ca.crt'
# Verify ssl in pg connect
conn = PG.connect(host:"MY_INSTANCE_ID", port: 6875, user: "materialize", sslmode: "verify-full")
res = conn.exec('select tablename from pg_tables;')
res.each do |row|
puts row['tablename']
end
Stream
To take full advantage of incrementally updated materialized views from a Ruby application, instead of querying Materialize for the state of a view at a point in time, use a TAIL
statement to request a stream of updates as the view changes.
To read a stream of updates from an existing materialized view, open a long-lived transaction with BEGIN
and use TAIL
with FETCH
to repeatedly fetch all changes to the view since the last query.
require 'pg'
# Locally running instance:
conn = PG.connect(host:"127.0.0.1", port: 6875, user: "materialize")
conn.exec('BEGIN')
conn.exec('DECLARE c CURSOR FOR TAIL my_view')
while true
conn.exec('FETCH c') do |result|
result.each do |row|
puts row
end
end
end
The TAIL Output format of res.rows
is an array of view update objects. When a row of a tailed view is updated, two objects will show up in the rows
array:
...
{"mz_timestamp"=>"1648126887708", "mz_diff"=>"1", "my_column"=>"1"}
{"mz_timestamp"=>"1648126887708", "mz_diff"=>"1", "my_column"=>"2"}
{"mz_timestamp"=>"1648126887708", "mz_diff"=>"1", "my_column"=>"3"}
{"mz_timestamp"=>"1648126897364", "mz_diff"=>"-1", "my_column"=>"1"}
...
An mz_diff
value of -1
indicates Materialize is deleting one row with the included values. An update is just a deletion (mz_diff: '-1'
) and an insertion (mz_diff: '1'
) with the same mz_timestamp
.
Query
Querying Materialize is identical to querying a traditional PostgreSQL database: Ruby executes the query, and Materialize returns the state of the view, source, or table at that point in time.
Because Materialize maintains materialized views in memory, response times are much faster than traditional database queries, and polling (repeatedly querying) a view doesn’t impact performance.
Query a view my_view
with a select statement:
require 'pg'
conn = PG.connect(host:"127.0.0.1", port: 6875, user: "materialize")
res = conn.exec('SELECT * FROM my_view')
res.each do |row|
puts row
end
For more details, see the exec
instance method documentation.
Insert data into tables
Most data in Materialize will stream in via a SOURCE
, but a TABLE
in Materialize can be helpful for supplementary data. For example, use a table to join slower-moving reference or lookup data with a stream.
Basic Example: Insert a row of data into a table named countries
in Materialize.
require 'pg'
conn = PG.connect(host:"127.0.0.1", port: 6875, user: "materialize")
conn.exec("INSERT INTO my_table (my_column) VALUES ('some_value')")
res = conn.exec('SELECT * FROM my_table')
res.each do |row|
puts row
end
Manage sources, views, and indexes
Typically, you create sources, views, and indexes when deploying Materialize, although it is possible to use a Ruby app to execute common DDL statements.
Create a source from Ruby
require 'pg'
conn = PG.connect(host:"127.0.0.1", port: 6875, user: "materialize")
# Create a source
src = conn.exec(
"CREATE SOURCE IF NOT EXISTS market_orders_raw FROM PUBNUB
SUBSCRIBE KEY 'sub-c-4377ab04-f100-11e3-bffd-02ee2ddab7fe'
CHANNEL 'pubnub-market-orders'"
);
puts src.inspect
# Show the source
res = conn.exec("SHOW SOURCES")
res.each do |row|
puts row
end
For more information, see CREATE SOURCE
.
Create a view from Ruby
require 'pg'
conn = PG.connect(host:"127.0.0.1", port: 6875, user: "materialize")
# Create a view
view = conn.exec(
"CREATE VIEW market_orders_2 AS
SELECT
val->>'symbol' AS symbol,
(val->'bid_price')::float AS bid_price
FROM (SELECT text::jsonb AS val FROM market_orders_raw)"
);
puts view.inspect
# Show the view
res = conn.exec("SHOW VIEWS")
res.each do |row|
puts row
end
For more information, see CREATE VIEW
.
Ruby ORMs
Materialize doesn’t currently support the full catalog of PostgreSQL system metadata API endpoints, including the system calls that object relational mapping systems (ORMs) like Active Record use to introspect databases and do extra work behind the scenes. This means that ORM system attempts to interact with Materialize will currently fail. Once full pg_catalog
support is implemented, the features that depend on pg_catalog
may work properly.