search

FlipsideCrypto / livequery-models

https://flipsidecrypto.github.io/livequery-models/
MIT License
3 stars 3 forks source link

readme

LiveQuery Functions Overview

LiveQuery is a powerful tool that enables users to interact with approved APIs, access utility functions for easy handling of complex blockchain data, and maintain best practices for usage. With LiveQuery Functions, users can access a variety of APIs, create JSON RPC requests, easily convert data types such as hex strings to integers, securely store encrypted credentials, and more. This resource offers guidance on limits, best practices, sample queries, and future enhancements to ensure effective use of the LiveQuery Functions.

Table of Contents

LiveQuery Functions

Function Name Purpose Status
live.udf_api Can interact directly with approved APIs Available
utils.udf_hex_to_int Converts hex strings to integers Available
utils.udf_hex_to_string Converts hex strings to text Available
utils.udf_json_rpc_call Creates JSON RPC requests Available
utils.udf_hex_encode_function Converts a function or event signature to hex Coming Soon
utils.udf_evm_decode_logs Decodes EVM log data Coming Soon

Live Functions

Limits and Best Practices

udf_api

This function can be used to interact directly with approved APIs, including QuickNode, DeFi Llama, and more. Please see the Approved APIs section below for a list of approved APIs.

Syntax

livequery.live.udf_api(
  [method,]
  url,
  [headers,]
  [data,]
  [secret_name]
)

Arguments

Required

Optional

Approved APIs

API Name API Docs Authentication Required
QuickNode Docs Yes
DeFi Llama Docs No
zkSync Docs No
DeepNFT Value Docs Yes
Zapper Docs No
Helius Docs No
Stargaze Name Service Docs No
Snapshot Docs No
Solscan Docs Yes
SubGraphs Docs Sometimes
IPFS Docs No

If you are interested in using an API that is not on this list, please reach out to us on Discord.

Sample Queries

QuickNode Examples ```sql -- Get the latest block number, please note you will need to register your node secrets -- See docs for more info on how to register secrets and use them in queries -- See docs for more info on how to create JSON RPC requests (utils.udf_json_rpc_call) WITH create_rpc_request AS ( SELECT livequery.utils.udf_json_rpc_call( 'eth_blockNumber', [] ) AS rpc_request ), base AS ( SELECT livequery.live.udf_api( 'POST', 'https://indulgent-smart-shape.discover.quiknode.pro/{url_key}/',{}, -- your words will likely be different. This is just an example URL. rpc_request, 'quicknode_eth' ) AS api_call FROM create_rpc_request ) SELECT api_call :data :result :: STRING AS hex_block, livequery.utils.udf_hex_to_int(hex_block) :: INT AS int_block FROM base; -- Get the latest balance for a wallet, please note you will need to register your node secrets -- This is a general ETH call example, and can be used for any contract and function WITH inputs AS ( -- input function_sig, token_address, wallet_address -- format data for eth call, should be 64 chars long (32 bytes) + 10 chars for function sig (including 0x) SELECT LOWER('0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84') AS token_address, -- stETH LOWER('0x66B870dDf78c975af5Cd8EDC6De25eca81791DE1') AS wallet_address, --a16Z '0x70a08231' AS function_sig, --balanceOf(address) CONCAT( function_sig, LPAD(REPLACE(wallet_address, '0x', ''), 64, 0) ) AS DATA ) -- creates a formatted json rpc eth_call request that is ready to be sent to a node , create_rpc_request as ( SELECT wallet_address, livequery.utils.udf_json_rpc_call( 'eth_call', [{ 'to': token_address, 'from': null, 'data': data },'latest'] ) AS rpc_request FROM inputs ) , base AS ( --sending request to node SELECT wallet_address, livequery.live.udf_api( 'POST', 'https://indulgent-smart-shape.discover.quiknode.pro/{url_key}/',{},rpc_request, --secret value in URL (URL Key). Your subdomain will likely be different. This is just an example URL. 'quicknode_eth' --registered secret name ) AS response from create_rpc_request ) SELECT wallet_address, livequery.utils.udf_hex_to_int(response:data:result::string) :: INT / pow(10,18) AS balance FROM base; ```
Subgraph Example ```sql -- Getting Univ3 Liquidity Data from a Subgraph -- Create a graphQL query and post it to the subgraph SELECT livequery.live.udf_api( 'POST', 'https://api.thegraph.com/subgraphs/name/messari/uniswap-v3-polygon',{ 'Content-Type': 'application/json' },{ 'query' :'{\n liquidityPools(first: 10, orderBy: totalLiquidity, orderDirection: desc) {\n id\n totalLiquidity\n name\n}\n}', 'variables':{}} ) AS response; -- format the response with base as ( SELECT livequery.live.udf_api( 'POST', 'https://api.thegraph.com/subgraphs/name/messari/uniswap-v3-polygon', {'Content-Type': 'application/json'}, {'query':'{\n liquidityPools(first: 10, orderBy: totalLiquidity, orderDirection: desc) {\n id\n totalLiquidity\n name\n}\n}', 'variables':{} } ) as response ) select value:id::string as address, value:name::string as name, value:totalLiquidity::int as totalLiquidity from base, lateral flatten (input => response:data:data:liquidityPools) ; ```
DeFi Llama API Example ```sql -- DeFI Llama does not require authentication, so we can just pass the URL SELECT livequery.live.udf_api('https://api.llama.fi/chains') as response; -- format the response WITH base AS ( SELECT livequery.live.udf_api('https://api.llama.fi/chains') AS response ) SELECT VALUE :chainId :: INT AS chainID, VALUE :cmcId :: INT AS cmcID, VALUE :gecko_id :: STRING AS geckoID, VALUE :name :: STRING AS NAME, VALUE :tokenSymbol :: STRING AS symbol, VALUE :tvl :: FLOAT AS tvl FROM base, LATERAL FLATTEN ( input => response :data ); ```

IPFS Example ```sql -- you can use this function to retrieve data from IPFS. You can find the hash in the URL within several places onchain, including evm logs and traces. SELECT livequery.live.udf_api('https://ipfs.io/ipfs/QmTFX3TopS8JsgpfBLKGDnTiaWrRcfStDWDQaREzD36sWW') AS response; ```

Utility Functions

Utility functions are designed to make your life easier when interacting with blockchain data.

udf_hex_to_int

This function converts a hex string to an integer.

Syntax

livequery.utils.udf_hex_to_int(
  [encoding,]
  hex
)

Arguments

Required

Optional

Sample Queries

Convert Hex to Integer ```sql -- these are all the same select livequery.utils.udf_hex_to_int ('1E240')::int as int1, livequery.utils.udf_hex_to_int ('0x1E240')::int as int2, livequery.utils.udf_hex_to_int ('hex','0x1E240')::int as int3; ```
Convert Hex to Signed 2's Complement Integer ```sql -- these are the same select livequery.utils.udf_hex_to_int ('s2c','FFFE1DC0')::int as int1, livequery.utils.udf_hex_to_int ('s2c','0xFFFE1DC0')::int as int2 ```

udf_hex_to_string

This function converts a hex string to a string of human readable characters. It will handle obscure characters like emojis and special characters.

Syntax

livequery.utils.udf_hex_to_string(
  hex
)

Arguments

Required

Sample Queries

Convert Hex to Text ```sql select livequery.utils.udf_hex_to_string('466C69707369646520726F636B73') as text1 ```

udf_json_rpc_call

This function creates a JSON RPC request based on the parameters provided.

Syntax

livequery.utils.udf_json_rpc_call(
  method,
  params
  [,id]
)

Arguments

Required

Optional

Sample Queries

Create eth_blockNumber Request ```sql -- creates a JSON RPC request to get the latest block number SELECT livequery.utils.udf_json_rpc_call('eth_blockNumber',[]) AS rpc_request; ```
Create eth_call Request ```sql -- this will create a balanceOf request for a16Z's wallet on stETH. Make sure to format the data correctly for the function you are calling. WITH inputs AS ( -- input function_sig, token_address, wallet_address -- format data for eth call, should be 64 chars long (32 bytes) + 10 chars for function sig (including 0x) SELECT LOWER('0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84') AS token_address, -- stETH LOWER('0x66B870dDf78c975af5Cd8EDC6De25eca81791DE1') AS wallet_address, --a16Z '0x70a08231' AS function_sig, --balanceOf(address) CONCAT( function_sig, LPAD(REPLACE(wallet_address, '0x', ''), 64, 0) ) AS DATA ) -- creates a formatted json rpc eth_call request that is ready to be sent to a node SELECT livequery.utils.udf_json_rpc_call( 'eth_call', [{ 'to': token_address, 'from': null, 'data': data },'latest'] ) AS rpc_request FROM inputs; ```

Registering Secrets

With LiveQuery you can safely store encrypted credentials, such as an API key, with Flipside. This allows you to securely reference your credentials in your queries without exposing them directly.

To register a secret, follow these steps:

  1. Visit Ephit to obtain an Ephemeral query that will securely link your API Endpoint to Flipside's backend. This will allow you to refer to the URL securely in our application without referencing it or exposing keys directly.
  2. Fill out the form and click Submit this Credential
  3. Paste the provided query into Flipside and query your node directly in the app with your submitted Credential ({my_key})

Registering a secret from Quicknode to query nodes directly in Flipside:

  1. Sign up for a free Quicknode API Account
  2. Navigate to Endpoints on the left hand side then click the Get Started tab and Copy the HTTP Provider Endpoint. Do not adjust the Setup or Security parameters.
  3. Follow the steps above to register your secret
  4. See live.udf_api for sample queries

    Other DBT docs - LiveQuery Models

Dbt repo for managing LiveQuery database.

Profile Set Up

Use the following within profiles.yml

livequery:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: <ACCOUNT>
      role: <ROLE>
      user: <USERNAME>
      password: <PASSWORD>
      region: <REGION>
      database: LIVEQUERY_DEV
      warehouse: <WAREHOUSE>
      schema: silver
      threads: 12
      client_session_keep_alive: False
      query_tag: <TAG>
    prod:
      type: snowflake
      account: <ACCOUNT>
      role: <ROLE>
      user: <USERNAME>
      password: <PASSWORD>
      region: <REGION>
      database: LIVEQUERY_DEV
      warehouse: <WAREHOUSE>
      schema: silver
      threads: 12
      client_session_keep_alive: False
      query_tag: <TAG>

Variables

To control the creation of UDF or SP macros with dbt run:

Default values are False

Dropping and creating udfs can also be done without running a model:

dbt run-operation create_udfs --vars '{"UPDATE_UDFS_AND_SPS":True}' --args '{"drop_":false}'
dbt run-operation create_udfs --vars '{"UPDATE_UDFS_AND_SPS":True}' --args '{"drop_":true}'

Resources

Applying Model Tags

Database / Schema level tags

Database and schema tags are applied via the add_database_or_schema_tags macro. These tags are inherited by their downstream objects. To add/modify tags call the appropriate tag set function within the macro.

{{ set_database_tag_value('SOME_DATABASE_TAG_KEY','SOME_DATABASE_TAG_VALUE') }}
{{ set_schema_tag_value('SOME_SCHEMA_TAG_KEY','SOME_SCHEMA_TAG_VALUE') }}

Model tags

To add/update a model's snowflake tags, add/modify the meta model property under config . Only table level tags are supported at this time via DBT.

{{ config(
    ...
    meta={
        'database_tags':{
            'table': {
                'PURPOSE': 'SOME_PURPOSE'
            }
        }
    },
    ...
) }}

By default, model tags are pushed to Snowflake on each load. You can disable this by setting the UPDATE_SNOWFLAKE_TAGS project variable to False during a run.

dbt run --var '{"UPDATE_SNOWFLAKE_TAGS":False}' -s models/core/core__fact_blocks.sql

Querying for existing tags on a model in snowflake

select *
from table(livequery.information_schema.tag_references('livequery.core.fact_blocks', 'table'));