IMPORTING AND SERVING MILLIONS OF RECORDS

TALE OF A PROTOTYPE

OXEANPEDIA

No content

fishing sailing science oil gas exploration shipping deep sea mining governmental use ecological monitoring

WHERE'S THE DATA?

11 DIFFERENT SOURCES

500K - 1M RECORDS / MONTH / SOURCE

BIG DATA?

No content

VVV

VOLUME. VELOCITY. VARIETY.

VOLUME FACEBOOK - 250 BILLION IMAGES

VELOCITY FACEBOOK - 100 MILLION IMAGES / DAY

VARIETY FACEBOOK - IMAGES, POSTS, PERSONAL MESSAGES, FRIENDSHIPS, LOCATIONS

EXTREMELY LARGE DATA SETS ANALYSED COMPUTATIONALLY TO REVEAL PATTERNS, TRENDS, AND ASSOCIATIONS

R&D DATABASE TYPE DECISION MAKING

POSTGRES VS AMAZON DYNAMODB VS GOOGLE BIG TABLE

POSTGRES

AMAZON DYNAMODB

GOOGLE BIG TABLE

PG FOR THE WIN

IMPORT

THE STUPID SIMPLE WAY: MEASUREMENT.CREATE()

ACTIVERECORDIMPORTER MEASUREMENT.IMPORT()

WWGD WHAT WOULD GEORGE DO?

HAMMERFIX POSTGRES COPY

COPY COMMAND COPY measurements FROM 'measurements.csv'

COPY COMMAND COPY measurements (temperature, latitude, longitude) FROM 'measurements.csv'

COPY IN RUBY db_conn = ActiveRecord::Base.connection.raw_connection db_conn.copy_data('COPY measurements FROM STDIN') do CSV.foreach('measurements.csv', headers: true) do |row| db_conn.put_copy_data(row.fields + [Time.zone.now]) end end

module Oxeanpedia module Argo class CopyData method_object :file_path CSV_FIELD_NAMES = [ 'ARGOS_ID', 'LATITUDE (degree_north)', 'LONGITUDE (degree_east)', 'PRES (decibar)', 'TEMP (degree_Celsius)' ] def call pgconn.copy_data(copy_statement, enco) do CSV.foreach(file_path, headers: true) do |row| pgconn.put_copy_data( ['argo'] + [date_field(row)] + row.fields(*CSV_FIELD_NAMES) + [file_path] ) end end end private def date_field(row) row['DATE (YYYY/MM/DD HH:MI:SS)'] || row['DATE (YYYY-MM-DDTHH:MI:SSZ)'] end def pgconn @pgconn ||= ActiveRecord::Base.connection.raw_connection end def time @time ||= Time.zone.now end def enco PG::TextEncoder::CopyRow.new end def copy_statement <<-HEREDOC.strip_heredoc COPY argo_measurements (source, measured_on, source_id, latitude, longitude, depth_in_meters, temperature_in_celsius, filename) FROM STDIN HEREDOC end end end end

TEMP TABLE

WITH moved_rows AS ( DELETE FROM argo_measurements am WHERE filename = '#{filename}' AND latitude IS NOT NULL AND longitude IS NOT NULL AND temperature_in_celsius IS NOT NULL AND depth_in_meters IS NOT NULL RETURNING am.* ) INSERT INTO measurements (source, source_id, latitude, longitude, depth_in_meters, temperature_in_celsius, measured_on, created_at, updated_at, location) SELECT source, source_id, latitude, longitude, depth_in_meters, temperature_in_celsius, measured_on, current_timestamp, current_timestamp, ST_SetSRID(ST_MakePoint(longitude, latitude),4326) as location FROM moved_rows ON CONFLICT (latitude, longitude, depth_in_meters, measured_on) DO NOTHING

GEO WTFS

No content

geometry ST_MakePoint(double precision x, double precision y);

ARCHITECTURE

No content

REACT ON RAILS

CLIENT: WHERE'S THE DESKTOP APP?

SERVING THE DATA

INDEXING

No content

7K ROWS PER REQUEST

COMPLETED 200 OK IN 2750MS (VIEWS: 2500MS | ACTIVERECORD: 250MS)

> PG string > PG Ruby row objects > ActiveRecord objects > ActiveRecordSerializer objects > JSON string as JSON API

PAGINATE?

CACHE?

JSON DIRECTLY FROM POSTGRES

SELECT json_build_object( 'data', json_agg( json_build_object( 'id', m.id, 'type', 'measurements', 'attributes', json_build_object( 'latitude', m.latitude, 'longitude', m.longitude, 'depth_in_meters', m.depth_in_meters, 'temperature_in_celsius', m.temperature_in_celsius, 'measured_on', m.measured_on ) ) ) ) as result FROM ..

COMPLETED 200 OK IN 302MS (VIEWS: 2MS | ACTIVERECORD: 300MS)

THAT'S ALL FOLKS!