Connections: Sync All Tables in Schema (Javascript)

1
// This script automates the synchronization of tables within a specified schema (Snowflake).
2
3
// Load environment variables from a specific .env file for configuration
4
require('dotenv').config({ path: 'sigma-api-recipes/.env' });
5
6
console.log('Environment Variables:', {
7
    baseURL: process.env.baseURL,
8
    CONNECTIONID: process.env.CONNECTIONID,
9
    SYNC_PATH: process.env.SYNC_PATH,
10
});
11
12
// Import the function to obtain a bearer token from the authenticate-bearer module
13
const getBearerToken = require('../get-access-token');
14
15
// Import Axios for making HTTP requests
16
const axios = require('axios');
17
18
// Load use-case specific variables from environment variables
19
const baseURL = process.env.baseURL; // Your base URL
20
const connectionId = process.env.CONNECTIONID; // Connection ID
21
let syncPaths;
22
let bearerToken; // Global variable to store the fetched bearer token
23
24
// Validate and parse SYNC_PATH
25
try {
26
    if (!process.env.SYNC_PATH) {
27
        throw new Error('SYNC_PATH is not defined in the .env file.');
28
    }
29
30
    // Parse SYNC_PATH as JSON
31
    syncPaths = JSON.parse(process.env.SYNC_PATH);
32
33
    if (!Array.isArray(syncPaths)) {
34
        throw new Error('SYNC_PATH must be a JSON array.');
35
    }
36
} catch (error) {
37
    console.error('Error parsing SYNC_PATH:', error.message);
38
    process.exit(1);
39
}
40
41
// Function to initialize the bearer token
42
async function initializeBearerToken() {
43
    bearerToken = await getBearerToken();
44
    if (!bearerToken) {
45
        console.error('Failed to obtain Bearer token.');
46
        process.exit(1);
47
    }
48
    console.log('Bearer token initialized successfully.');
49
}
50
51
// Function to resolve the `inodeId` for a schema/folder
52
async function lookupInodeId(path) {
53
    const endpoint = `${baseURL}/connection/${connectionId}/lookup`;
54
    console.log(`Looking up inodeId for path: ${JSON.stringify(path)} at URL: ${endpoint}`);
55
56
    try {
57
        const response = await axios.post(endpoint, { path }, {
58
            headers: { 'Authorization': `Bearer ${bearerToken}`, 'Content-Type': 'application/json' },
59
        });
60
61
        const { inodeId, kind } = response.data;
62
        if (!inodeId || !kind) {
63
            console.error(`Unexpected response: ${JSON.stringify(response.data)}`);
64
            return null;
65
        }
66
67
        console.log(`Resolved inodeId: ${inodeId} (kind: ${kind}) for path: ${JSON.stringify(path)}`);
68
        return { inodeId, kind };
69
    } catch (error) {
70
        console.error('Error resolving inodeId:', error.message);
71
        if (error.response) {
72
            console.error('Response Data:', error.response.data);
73
        }
74
        return null;
75
    }
76
}
77
78
// Function to list tables under a given inodeId
79
async function listTables(parentInodeId) {
80
    const endpoint = `${baseURL}/files?typeFilters=table&parentId=${parentInodeId}`;
81
    console.log(`Fetching tables for parentInodeId: ${parentInodeId} at URL: ${endpoint}`);
82
83
    try {
84
        const response = await axios.get(endpoint, {
85
            headers: { 'Authorization': `Bearer ${bearerToken}`, 'Accept': 'application/json' },
86
        });
87
88
        const tables = response.data.entries || [];
89
        console.log(`Found ${tables.length} tables under inodeId: ${parentInodeId}`);
90
        tables.forEach((table) => {
91
            console.log(`Table Name: ${table.name}, Table ID: ${table.id}`);
92
        });
93
94
        return tables.map((table) => ({
95
            id: table.id,
96
            name: table.name, // Include table name for path construction
97
        }));
98
    } catch (error) {
99
        console.error(`Error listing tables for inodeId: ${parentInodeId}`, error.message);
100
        if (error.response) {
101
            console.error('Response Data:', error.response.data);
102
        }
103
        return [];
104
    }
105
}
106
107
// Function to sync a specific table using its inodeId and full path
108
async function syncTable(inodeId, fullPath) {
109
    const endpoint = `${baseURL}/connections/${connectionId}/sync`;
110
111
    const payload = {
112
        path: fullPath, // Send the full path including the table name
113
    };
114
115
    try {
116
        console.log(`Starting sync for table with path: ${JSON.stringify(fullPath)}`);
117
        const response = await axios.post(endpoint, payload, {
118
            headers: { 'Authorization': `Bearer ${bearerToken}`, 'Content-Type': 'application/json' },
119
        });
120
121
        console.log(`Sync completed for table with path: ${JSON.stringify(fullPath)}`);
122
        console.log('Response:', response.data);
123
    } catch (error) {
124
        console.error(`Error syncing table with inodeId: ${inodeId}`, error.message);
125
        if (error.response) {
126
            console.error('Response Data:', error.response.data);
127
        }
128
    }
129
}
130
131
// Main function to list and sync tables
132
async function syncAllTables() {
133
    console.log(`Starting sync for path: ${JSON.stringify(syncPaths)}`);
134
135
    // Step 1: Resolve inodeId for the sync path
136
    const { inodeId } = await lookupInodeId(syncPaths);
137
    if (!inodeId) {
138
        console.error('Failed to resolve inodeId for path.');
139
        return;
140
    }
141
142
    // Step 2: List tables under the resolved inodeId
143
    const tables = await listTables(inodeId);
144
    if (tables.length === 0) {
145
        console.log('No tables found to sync.');
146
        return;
147
    }
148
149
    console.log(`Found ${tables.length} tables to sync.`);
150
151
    // Step 3: Sync each table
152
    for (const table of tables) {
153
        const fullPath = [...syncPaths, table.name]; // Append table name to sync path
154
        await syncTable(table.id, fullPath);
155
    }
156
}
157
158
// Execute the function if this script is run directly
159
if (require.main === module) {
160
    (async () => {
161
        await initializeBearerToken(); // Fetch the bearer token once
162
        await syncAllTables();
163
    })();
164
}

1
Starting sync for table with path: ["DATABASE","SCHEMA","TABLE"]
2
Sync completed for table with path: ["DATABASE","SCHEMA","TABLE"]
3
Response: {}