BEEM Gateway: Installation and Configuration

The BEEM Gateway is a lightweight, self-contained agent deployed inside your network. It handles secure data extraction from on-premises sources (databases, files, SFTP) and data delivery to on-premises destinations (e.g., Microsoft SQL Server). Data is transferred through an S3 bucket in your dedicated BEEM environment.

📘

For a high-level overview of pushing data to on-premises databases, see Gateway Destinations: Writing to On-Premises Databases.

Requirements

• OS: Windows x64
• Network: Outbound access to AWS S3 (ca-central-1) and CloudWatch Logs
• Credentials: AWS access key (provided by BEEM) or EC2 instance profile

Installation

schtasks /create /tn "BEEM-Gateway" /tr "C:\beem-gateway\beem-gateway.exe" /sc daily /st 23:00 /F /rl highest /ru SYSTEM

Configuration File

The gateway reads its source and destination configuration from a config.json file located in the same directory as the executable.

Sources

Sources define where the gateway extracts data from. Extracted data is uploaded to S3 and ingested into the BEEM warehouse.

Database Source (MSSQL)

{
  "Sources": [
    {
      "Type": "Mssql",
      "Name": "erp-database",
      "Server": "sql-server.local",
      "Port": 1433,
      "Database": "MyDatabase",
      "User": "beem_reader",
      "Password": "••••••••",
      "Tables": ["dbo.Customers", "dbo.Orders"]
    }
  ]
}

File Source (Local or SFTP)

{
  "Sources": [
    {
      "Type": "File",
      "Name": "local-exports",
      "Path": "/data/exports",
      "Regex": ".*\\.csv$"
    },
    {
      "Type": "Sftp",
      "Name": "vendor-sftp",
      "Server": "sftp.example.com",
      "Port": 22,
      "Username": "user",
      "Password": "••••••••",
      "Path": "/data/exports",
      "Regex": ".*\\.csv$"
    }
  ]
}

Destinations

Destinations define where the gateway pushes data to. Data is read from your BEEM Redshift warehouse and written to an on-premises target database.

MSSQL Destination

{
  "Destinations": [
    {
      "Type": "Mssql",
      "Name": "analytics-push",
      "Redshift": {
        "Host": "cluster.abc123.ca-central-1.redshift.amazonaws.com",
        "Port": 5439,
        "Database": "analytics",
        "User": "readonly_user",
        "Password": "••••••••"
      },
      "Mssql": {
        "Server": "sql-server.local",
        "Port": 1433,
        "Database": "TargetDB",
        "User": "beem_writer",
        "Password": "••••••••"
      },
      "Tables": [
        "public.dim_customer",
        "revenue_summary:SELECT customer_id, SUM(amount) as total FROM public.orders GROUP BY customer_id"
      ],
      "CreateIfNotExists": true,
      "Truncate": true
    }
  ]
}

How Destination Transfers Work

Each destination transfer uses a staging-table approach for reliability:

1. Schema is read from Redshift (column names, types, nullability)
2. If CreateIfNotExists is enabled, the target table is created in MSSQL
3. A temporary staging table is created in SQL Server
4. Data is streamed row-by-row from Redshift into the staging table (constant memory usage)
5. In a single transaction: the target is optionally truncated, then all rows are inserted from staging
6. On failure, the transaction rolls back — the target table remains unchanged

Type Mapping (Redshift to MSSQL)

Monitoring

• A log file is generated locally and uploaded to S3 after each run
• Logs are also sent to AWS CloudWatch under the log group matching your instance name
• When DataObjectIds are configured, EventBridge lifecycle events (START / SUCCESS / FAIL) are emitted and appear in BEEM's Monitor section

IAM Requirements

The BEEM team provides pre-configured AWS credentials. The gateway user requires:

• S3: PutObject, GetObject, ListBucket on the designated gateway bucket
• CloudWatch Logs: CreateLogGroup, CreateLogStream, PutLogEvents, and related read permissions