sources/usage-monitor/usage-monitor.mjs
import { DEFAULT_POLLING_SOURCE_TIMER_INTERVAL } from "@pipedream/platform"; import snowflake from "../../snowflake.app.mjs"; export default { type: "source", key: "snowflake-usage-monitor", name: "New Usage Monitor", description: "Emit new event when a query is executed in the specified params", version: "0.1.3", dedupe: "unique", props: { snowflake, db: "$.service.db", timer: { description: "Watch for changes on this schedule", type: "$.interface.timer", default: { intervalSeconds: DEFAULT_POLLING_SOURCE_TIMER_INTERVAL, }, }, warehouse: { type: "string", label: "Warehouse", description: "The warehouse to monitor", optional: true, }, database: { type: "string", label: "Database Name", description: "The name of the database to monitor", optional: true, }, username: { type: "string", label: "Username", description: "The username to monitor", optional: true, }, role: { type: "string", label: "Role", description: "The role to monitor", optional: true, }, schema: { type: "string", label: "Schema", description: "The schema to monitor", optional: true, }, totalElapsedTime: { type: "string", label: "Total Elapsed Time", description: "Show register where TOTAL_ELAPSED_TIME >= requested value", optional: true, }, bytesScanned: { type: "string", label: "Bytes Scanned", description: "Show register where BYTES_SCANNED >= requested value", optional: true, }, rowsProduced: { type: "string", label: "Rows Produced", description: "Show register where ROWS_PRODUCED >= requested value", optional: true, }, compilationTime: { type: "string", label: "Compilation Time", description: "Show register where COMPILATION_TIME >= requested value", optional: true, }, executionTime: { type: "string", label: "Execution Time", description: "Show register where EXECUTION_TIME >= requested value", optional: true, }, queuedProvisioningTime: { type: "string", label: "Queued Provisioning Time", description: "Show register where QUEUED_PROVISIONING_TIME >= requested value", optional: true, }, queueRepairTime: { type: "string", label: "Queue Repair Time", description: "Show register where QUEUE_REPAIR_TIME >= requested value", optional: true, }, queuedOverloadTime: { type: "string", label: "Queued Overload Time", description: "Show register where QUEUED_OVERLOAD_TIME >= requested value", optional: true, }, transactionBlockedTime: { type: "string", label: "Transaction Blocked Time", description: "Show register where TRANSACTION_BLOCKED_TIME >= requested value", optional: true, }, creditsUsedCloudServices: { type: "string", label: "Credit Used Cloud Services", description: "Show register where CREDITS_USED_CLOUD_SERVICES >= requested value", optional: true, }, }, methods: { updateLastExecutionTime() { this.db.set("lastExecutionTime", Date.now()); }, getLastExecutionTime() { return this.db.get("lastExecutionTime"); }, getSqlStatement() { const whereClausules = []; const binds = []; if (this.database) { whereClausules.push("and DATABASE_NAME = ?"); binds.push(this.database); } if (this.warehouse) { whereClausules.push("and WAREHOUSE_NAME = ?"); binds.push(this.warehouse); } if (this.username) { whereClausules.push("and user_name = ?"); binds.push(this.username); } if (this.role) { whereClausules.push("and role_name = ?"); binds.push(this.role); } if (this.schema) { whereClausules.push("and schema_name = ?"); binds.push(this.schema); } if (this.totalElapsedTime) { whereClausules.push("and TOTAL_ELAPSED_TIME >= ?"); binds.push(this.totalElapsedTime); } if (this.bytesScanned) { whereClausules.push("and BYTES_SCANNED >= ?"); binds.push(this.bytesScanned); } if (this.rowsProduced) { whereClausules.push("and ROWS_PRODUCED >= ?"); binds.push(this.rowsProduced); } if (this.compilationTime) { whereClausules.push("and COMPILATION_TIME >= ?"); binds.push(this.compilationTime); } if (this.executionTime) { whereClausules.push("and EXECUTION_TIME >= ?"); binds.push(this.executionTime); } if (this.queuedProvisioningTime) { whereClausules.push("and QUEUED_PROVISIONING_TIME >= ?"); binds.push(this.queuedProvisioningTime); } if (this.queueRepairTime) { whereClausules.push("and QUEUE_REPAIR_TIME >= ?"); binds.push(this.queueRepairTime); } if (this.queuedOverloadTime) { whereClausules.push("and QUEUED_OVERLOAD_TIME >= ?"); binds.push(this.queuedOverloadTime); } if (this.transactionBlockedTime) { whereClausules.push("and TRANSACTION_BLOCKED_TIME >= ?"); binds.push(this.transactionBlockedTime); } if (this.creditsUsedCloudServices) { whereClausules.push("and CREDITS_USED_CLOUD_SERVICES >= ?"); binds.push(this.creditsUsedCloudServices); } return { sqlText: `select * from table(information_schema.query_history()) where START_TIME > ? ${whereClausules.join(" ")} order by start_time desc limit 50`, binds: [ new Date(this.getLastExecutionTime()).toISOString(), ...binds, ], }; }, async fetchData() { return this.snowflake.executeQuery(this.getSqlStatement()); }, emit(event) { this.$emit(event, { summary: event.QUERY_TEXT, id: event.QUERY_ID, ts: event.START_TIME, }); }, }, hooks: { async deploy() { this.updateLastExecutionTime(); }, }, async run() { const rows = await this.fetchData(); for await (const row of rows) { this.emit(row); } this.updateLastExecutionTime(); }, };
Chase Roberts@chsrbrts@benedictevans If you haven’t used @pipedream yet, then you haven’t lived.
✨Ellie Day✨@heyelliedayEvaluation update: @pipedream has quite literally been a dream to work with! I’m excited to leverage this tool for all the various workflows I need to write. I’m currently at 11k invocations a day from the initial workflows I’ve written in the past couple weeks.
Michael Braedley@MBraedleyUpdate: I got it working properly, and it's working so well that I'm dropping IFTTT. @pipedream can do everything that IFTTT basic can, and most (if not all things) IFTTT pro can for free or at a reasonable price if you need it. I am recommending it for basically any power user.
Thomas Cutting@mrthomascuttingWant quick+dirty integrations for a serverless workflow - @pipedream is my new go-to 😃
Matthew Roberts@mattdotrobertsday 013 - finally hit node js. This is the secret sauce of taking #nocode projects that one step further. Pumped about getting deeper into @pipedream now
Kenneth Auchenberg 💭@auchenbergYahoo Pipes is back! Kinda 😍 @pipedream
Raymond Camden 🥑@raymondcamdenAwesome video by the @pipedream folks showing real time twitter sentiment analysis integrated with Google Sheets. This is where Pipedream *really* shines, connecting systems together in easy workflows.
Nacho Caballero@nachocaballeroI couldn't recommend @pipedream more. It's an amazing service to integrate different APIs. Much more powerful than Zapier and more user-friendly than AWS Lambda. I'm very proud to wear this t-shirt #NoCode
Jason Snow@jyksnwDeveloped a working prototype environmental sensor IoT solution with @particle Photon, @pipedream, and @MongoDB with full graphing and alerting in less than a day! All amazing technology, will def. be exploring these more.
Steven Terrana@steven_terrana@burgwyn you've inspired me to finally set up my own blog. I'll make sure my first blog post explains the tech behind the setup. think @obsdmd + @GatsbyJS + @pipedream.
🚄 James Augeri, PhD@DotDotJamesWant to low-code your back end, need more horsepower than @Bubble / @KnackHQ, or just miss Yahoo! Pipes? Check out @PipeDream
Sébastien Chopin@AtinuxGitHub issues should be like @linear_app for maintainers. Looking forward more integrations with GH actions or tools like @pipedream 👀
Raul@raul_predescuIf you're a dev and not using @pipedream, you're missing out. Been using it for months, daily. FREE for devs. Plenty of integrations and good limits. Absolutely love it.
Bruno Skvorc@bitfallsSo @pipedream is pretty amazing. In 3 minutes I just made a flow which adds @rickastley's Never Gonna Give You Up to my @spotify playlist whenever a new pull request arrives in an old repo of mine.
Zach Lanich@ZachLanichUm, wow 🤯 @pipedream
Steven Bell@bellontechI just used @pipedream to build a Shopify App. Wow, they make small backed tasks easy.
Jay Hack 🎩🇺🇸@_jayhack_Very impressed with this bad boi - it reminds me of a @PalantirTech internal tool, but geared towards integrations instead of data analysis and far more customizable. Great expectations here 🚀🤩
Tree Sturgeon 🔥🚴♂️🌳@philsturgeonFor context this is day 2 of a really challenging and stupid migration from Notion to @airtable with disparate/missing data. It's going better than expected and thanks to @pipedream I don't have to bother the iOS dev to add W3W.