http://starrocks.io logo
Join Slack
Powered by
l
@Rocky hello
r
Hello! I'm Rocky. How can I help you with StarRocks today?
l
@Rocky grant select on af_etl_data_log table in database dwh to user 'hanth5'@'%'; correct?
r
The syntax you provided is not quite correct for StarRocks. In StarRocks, you should specify the database using the 
db_name.table_name
format and place the 
TABLE
keyword before the name. The correct command is:
Copy code
sql
GRANT SELECT ON TABLE dwh.af_etl_data_log TO USER 'hanth5'@'%';
Key Differences: * Database Reference: Instead of 
table_name IN DATABASE db_name
, StarRocks uses the standard 
db_name.table_name
notation. * Keyword Order: The keyword 
TABLE
must come before the table name (i.e., 
ON TABLE <name>
), not after it. * Context: Ensure you are in the correct catalog (usually 
default_catalog
) before running the command, or use the full three-part name: 
default_catalog.dwh.af_etl_data_log
. References • sql-reference/sql-statements/account-management/GRANT.md
l
@Rocky CREATE TABLE public.logcall_crm ( id serial4 NOT NULL, queue_id varchar(50) NULL, phone_number varchar(20) NULL, ext_answer varchar(20) NULL, call_status varchar(50) NULL, id_snowflake varchar(250) NULL, id_data int8 NULL, campaign_id varchar(250) NULL, start_time timestamp NULL, ringing_time int4 NULL, waiting_time int4 NULL, talking_time int4 NULL, created_at timestamp DEFAULT CURRENT_TIMESTAMP NULL, "data" jsonb NULL, update_at timestamp DEFAULT CURRENT_TIMESTAMP NULL, is_send varchar(255) NULL, destination_caller varchar(255) NULL, recording_url varchar(255) NULL, ext_no_answer _varchar NULL, CONSTRAINT logcall_crm_pkey PRIMARY KEY (id) ); how starrock data type like _varchar in postgres
r
In StarRocks, the PostgreSQL 
_varchar
type (which is an alias for 
character varying[]
or an array of varchars) is represented using the 
ARRAY<VARCHAR>
 data type. Here is how your table would look translated to StarRocks syntax: Recommended StarRocks Mapping
Copy code
sql
CREATE TABLE public.logcall_crm (
    id              INT NOT NULL,           -- serial4 maps to INT
    queue_id        VARCHAR(50),
    phone_number    VARCHAR(20),
    ext_answer      VARCHAR(20),
    call_status     VARCHAR(50),
    id_snowflake    VARCHAR(250),
    id_data         BIGINT,                 -- int8 maps to BIGINT
    campaign_id     VARCHAR(250),
    start_time      DATETIME,               -- timestamp maps to DATETIME
    ringing_time    INT,                    -- int4 maps to INT
    waiting_time    INT,
    talking_time    INT,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    data            JSON,                   -- jsonb maps to JSON
    update_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_send         VARCHAR(255),
    destination_caller VARCHAR(255),
    recording_url   VARCHAR(255),
    ext_no_answer   ARRAY<VARCHAR(65533)>   -- _varchar maps to ARRAY<VARCHAR>
) 
PRIMARY KEY (id)
DISTRIBUTED BY HASH(id);
Key Mapping Details: 1. 
_varchar
$\rightarrow$ 
ARRAY<VARCHAR(len)>
: StarRocks supports the 
ARRAY
complex type. You must specify the element type inside the brackets. 2. 
jsonb
$\rightarrow$ 
JSON
: StarRocks has a native 
JSON
type that is optimized for performance, similar to PostgreSQL's 
jsonb
. 3. 
serial4
$\rightarrow$ 
INT
: 
serial
types in PostgreSQL are essentially integers with an auto-incrementing sequence. In StarRocks, you can use 
INT
or 
BIGINT
. If you need auto-generation, you can add the 
AUTO_INCREMENT
attribute (supported on Primary Key tables). 4. 
timestamp
$\rightarrow$ 
DATETIME
: StarRocks uses 
DATETIME
for date and time values. 5. 
_
(Underscore) Prefix: In PostgreSQL, the underscore prefix (
_int4
, 
_varchar
) is the internal naming convention for array types. In StarRocks, you always use the explicit 
ARRAY<type>
syntax. Limitations to Note: * Array as Key: In StarRocks, 
ARRAY
columns cannot be used as Primary Keys or Partition Keys. * Varchar Length: StarRocks 
VARCHAR
requires a length (max 1,048,576 for version 3.0+, but traditionally 65533). For the array elements, 
ARRAY<VARCHAR(65533)>
is a safe default for a safe "max" size. References • sql-reference/data-types/semi_structured/Array.md • data_source/External_table.md • sql-reference/data-types/semi_structured/JSON.md ) References • sql-reference/data-types/semi_structured/Array.md • sql-reference/data-types/semi_structured/JSON.md
l
how to read this table from airflow and streamload to starrock this table with ARRAY<VARCHAR
@Rocky how to read this table from airflow and streamload to starrock this table with ARRAY<VARCHAR
r
To load data from PostgreSQL into StarRocks, especially when dealing with complex types like 
ARRAY<VARCHAR>
and 
JSON
, the most reliable method is to use Stream Load with the JSON format. Here is a step-by-step approach using an Airflow DAG. 1. Strategy * Source: Use 
PostgresHook
to fetch data from PostgreSQL. * Format: Convert the records into NDJSON (Newline Delimited JSON). This is superior to CSV for this use case because it natively handles the PostgreSQL 
_varchar
(array) and 
jsonb
columns without complex escaping. * Destination: Use the StarRocks Stream Load API via the 
requests
library. 2. Airflow DAG Example
Copy code
python
import json
from datetime import datetime
import requests
from airflow import DAG
from airflow.operators.python import PythonOperator
from airflow.providers.postgres.hooks.postgres import PostgresHook
from airflow.hooks.base import BaseHook

def pg_to_starrocks_stream_load():
    # 1. Get data from PostgreSQL
    pg_hook = PostgresHook(postgres_conn_id='postgres_default')
    # Convert array and jsonb to format compatible with JSON serialization
    sql = "SELECT id, queue_id, phone_number, data, ext_no_answer FROM public.logcall_crm"
    df = pg_hook.get_pandas_df(sql)

    # 2. Prepare NDJSON data
    # Pandas will convert Postgres arrays (_varchar) to Python lists
    # and jsonb to Python dicts. to_json(orient='records', lines=True) 
    # creates the Newline Delimited JSON StarRocks expects.
    ndjson_data = df.to_json(orient='records', lines=True)

    # 3. Stream Load Configuration
    sr_conn = BaseHook.get_connection('starrocks_default') # Define this in Airflow Connections
    url = f"http://{sr_conn.host}:{sr_conn.port}/api/public/logcall_crm/_stream_load"
    
    headers = {
        "format": "json",
        "strip_outer_array": "false", # NDJSON uses false; if sending a single [list], use true
        "Expect": "100-continue",
        "label": f"load_logcall_{datetime.now().strftime('%Y%m%d%H%M%S')}"
    }

    # 4. Execute Stream Load
    response = requests.put(
        url,
        data=ndjson_data,
        headers=headers,
        auth=(sr_conn.login, sr_conn.password),
        timeout=300
    )

    result = response.json()
    if result.get("Status") != "Success":
        raise Exception(f"Stream Load failed: {result.get('Message')}")
    print(f"Successfully loaded {result.get('NumberLoadedRows')} rows.")

with DAG(
    'pg_to_starrocks_load',
    start_date=datetime(2023, 1, 1),
    schedule_interval=None,
    catchup=False
) as dag:
    load_task = PythonOperator(
        task_id='stream_load_logcall',
        python_callable=pg_to_starrocks_stream_load
    )
3. Why this works for 
ARRAY<VARCHAR>
 * Postgres 
_varchar
: When queried via Python/Pandas, this arrives as a standard Python list (e.g., 
['val1', 'val2']
). * NDJSON Serialization: 
df.to_json(orient='records', lines=True)
turns that row into a JSON line: 
{"id": 1, "ext_no_answer": ["101", "102"], "data": {"key": "value"}}
* StarRocks Parsing: When StarRocks receives 
format: json
, it maps the JSON array 
["101", "102"]
directly into the 
ARRAY<VARCHAR>
column and the JSON object into the 
JSON
column without any manual type conversion. 4. Important Header Notes * 
format: json
: Required for StarRocks to parse JSON keys. * 
strip_outer_array
: * Set to 
false
if your payload is NDJSON (one JSON object per line). * Set to 
true
if your payload is a single large JSON Array 
[{...}, {...}]
. * 
columns
: Not required if your JSON keys exactly match your StarRocks table column names. If they differ, use: 
-H "columns: id, ext_no_answer, data"
References • sql-reference/data-types/semi_structured/Array.md • loading/Json_loading.md
4 Views
Open in Slack
PreviousNext
Powered by