Java sample to backfill meters from Postgres

This sample illustrates how to backfill meters from historic data stored in Postgres using the JAVA SDK.

The sample performs the following tasks:

  1. Read historical data from a Postgres table of tasks. The sample uses tasks sent and accepted by customers as meters.
  2. For each row, ingest the task sent or accepted meters into Amberflo depending on the task status. The uniqueId is set during ingestion to prevent duplicate ingestion records.

Maven dependencies (check for latest versions):


Java code:

import com.amberflo.metering.ingest.MeteringContext;
import com.amberflo.metering.ingest.meter_message.Domain;
import com.amberflo.metering.ingest.meter_message.MeterMessage;
import com.amberflo.metering.ingest.meter_message.MeterMessageBuilder;
import com.amberflo.metering.ingest.meter_message.Region;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import static com.amberflo.metering.ingest.MeteringContext.metering;

public class TasksPostgresBackfill {
    //TODO: set the database connection, queries, and Amberflo info
    final static String DB_HOST = "";
    final static String DB_NAME = "sampleDb";
    final static String DB_PASS = "sample-pass";
    final static String DB_USER = "sample-user";
    final static String SCHEMA = "sampleSchema";
    final static String COUNT_QUERY = "SELECT count(*)  AS total FROM tasks where status in ('sent', 'accepted');";
    final static String SELECT_QUERY = "SELECT id, tasks_type, user_id, status, email_sent_date, accepted_date FROM tasks where status in ('sent', 'accepted');";
    final static String AMBERFLO_API_KEY = "amberflo-api-key";
    //END TODO

    final static String TASKS_SENT_METER = "tasks_sent";
    final static String TASKS_ACCEPTED_METER = "tasks_accepted";
    static int counter_sent = 0;
    static int counter_accepted = 0;

    public static void main(final String[] args) throws Exception {
        Statement stmt = null;
        Connection con = null;
        ResultSet rs = null;
        try {

            final String url = String.format("jdbc:postgresql://%s:5432/%s?currentSchema=%s", DB_HOST, DB_NAME, SCHEMA);
            con = DriverManager.getConnection(url, DB_USER, DB_PASS);
            stmt = con.createStatement();
            rs = stmt.executeQuery(COUNT_QUERY);
            int total = rs.getInt("total");
            System.out.println("Total tasks sent and accepted = " + total);

            rs = stmt.executeQuery(SELECT_QUERY);
            while ( {
                final int tasksId = rs.getInt("id");
                final String customerId = rs.getString("user_id");
                final String tasksType = rs.getString("tasks_type");
                final String status = rs.getString("status");
                final Date emailSentDate = rs.getDate("email_sent_date");
                final Date acceptedDate = rs.getDate("accepted_date");
                System.out.println(String.format("tasksId=%s, status=%s, customerId=%s, tasksType=%s, emailSentDate=%s, acceptedDate=%s",
                        tasksId, status, customerId, tasksType, emailSentDate, acceptedDate));
                if ("sent".equals(status)) {
                    ingestMeterForTask(Integer.toString(tasksId), customerId, tasksType, TASKS_SENT_METER, emailSentDate);
                    if (counter_sent % 100 == 0 && counter_sent > 0) {
                } else if ("accepted".equals(status)) {
                    ingestMeterForTask(Integer.toString(tasksId), customerId, tasksType, TASKS_ACCEPTED_METER, acceptedDate);
                    if (counter_accepted % 100 == 0 && counter_accepted > 0) {
            System.out.println("DONE!!! total tasks sent ingested = " + counter_sent);
            System.out.println("DONE!!! total tasks accepted ingested = " + counter_accepted);
        } catch (Exception ex) {
        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (con != null) con.close();

    public static void ingestMeterForTask(
            final String tasksId,
            final String customerId,
            final String tasksType,
            final String meterName,
            final Date eventDate) {
        try {

            //java.sql.Date supports only Date components
            final LocalDateTime eventTime =
                    new java.util.Date(eventDate.getTime())

            final Map<String, String> dimensions = new HashMap();
            dimensions.put("tasksType", tasksType);

            //set uniqueId to prevent duplicate ingested records in case this code
            //is run multiple times
            final MeterMessage meterMessage = MeterMessageBuilder
                    .createInstance(meterName, eventTime, customerId)

            System.out.println(String.format("Amberflo ingestion: meter %s, task id %s, customerId %s, tasksType %s, eventTime %s",
                    meterName, tasksId, customerId,  dimensions.get("tasksType"), eventTime));
        } catch (Exception ex) {