**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.**
- The `udf_api` function is very powerful, but it is also very easy to abuse. Please be mindful of the following limits and best practices when using this function.
- We reserve the right to disable the `udf_api` function for particular users, or as a whole, if we see it being abused.
- Most APIs have rate limits. Please be mindful of these limits and do not abuse them.
- Most of the limits you will encounter using this function will be on the API side. Please be sure to thoroughly read an API's documentation before using it.
- **However, certain limits do apply to the `udf_api` function itself, including:**
- This means that if you run the same query twice, that API will be called twice. A future enhancement may address this need, but for now, please be mindful of this limitation.
- Please see the [secret registration section](#registering-secrets) below for more information on how to register secrets for use with the `udf_api` function.
- Technically, you can pass secrets into the `udf_api` function directly, but this is not recommended.
- If you do pass your secrets without following the steps in the [secret registration section](#registering-secrets), your secrets will be visible in Flipside's internal query history.
- These docs and this process will continue to evolve. More detailed examples and powerful use cases will continue to be added. We are just getting started!
- Please be patient with us as we work to improve this process.
- Upcoming enhancements include:
- Support for more APIs
- Secret management improvements
- If you build something that you believe is powerful enough to be included in this documentation, please reach out to us on [Discord](https://discord.com/channels/784442203187314689/1095714436599267409)! We would love to hear feedback and see what you are building.
## udf_api
This function can be used to interact directly with approved APIs, including QuickNode, DeFi Llama, and more. Please see the [Approved APIs](#approved-apis) section below for a list of approved APIs.
### Syntax
```sql
livequery.live.udf_api(
[method,]
url,
[headers,]
[data,]
[secret_name]
)
```
### Arguments
**Required**
-`url` (string): The URL to call. If you are doing a GET request that does not require authentication, you can pass the URL directly. Otherwise, you may need to pass in some or all of the optional arguments below. You may also need to pass a secret value into the URL if you are using an API that requires authentication. See the QuickNode example below for more information on this case.
**Optional**
-`method` (string): The HTTP method to use (GET, POST, etc.)
-`headers` (object): A JSON object containing the headers to send with the request
-`data` (object): A JSON object containing the data to send with the request. Batched JSON RPC requests are supported by passing an array of JSON RPC requests.
-`secret_name` (string): The name of the secret to use for authentication. Please see the [secret registration section](#registering-secrets) below for more information.
| SubGraphs | [Docs](https://thegraph.com/docs/en/querying/querying-the-graph/) | Sometimes |
| IPFS | [Docs](https://docs.ipfs.tech/reference/http/api/) | No |
If you are interested in using an API that is not on this list, please reach out to us on [Discord](https://discord.com/channels/784442203187314689/1095714436599267409).
---
### Sample Queries
<details>
<summary>QuickNode Examples</summary>
```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
-- 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;
```
</details>
<details>
<summary>Subgraph Example</summary>
```sql
-- Getting Univ3 Liquidity Data from a Subgraph
-- Create a graphQL query and post it to the subgraph
-- 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
);
```
</details>
</details>
<details>
<summary>IPFS Example</summary>
```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.
-`encoding` (string): The encoding to use. Valid values are `s2c` and `hex`. This parameter is optional. If not provided, the function will default to `hex`.
### Sample Queries
<details>
<summary>Convert Hex to Integer</summary>
```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;
```
</details>
<details>
<summary>Convert Hex to Signed 2's Complement Integer</summary>
```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
-- 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;
```
</details>
---
# 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](https://science.flipsidecrypto.xyz/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](https://flipside.new) 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](https://www.quicknode.com/core-api)
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](#udf_api) for sample queries
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.
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.
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.
