> ## Documentation Index
> Fetch the complete documentation index at: https://developers.stellar.org/llms.txt
> Use this file to discover all available pages before exploring further.

# Backfill using JS UDF

> This document outlines methods to extract required fields from the XDR of raw data.

This document outlines methods to extract required fields from the XDR of raw data.

Use Cases:

* Backfix for a bugfix
* Backfix for a new column added
* Temporary extraction of fields not parsed by stellar-etl

We'll take the example of extracting the `fee_account_muxed` field from a transaction envelope (`tx_meta` XDR). However, this method can be adapted to other fields as well. It is worth noting that most users will not need to standup and run their own Hubble. The Stellar Development Foundation provides public access to the data through the public datasets and tables in GCP BigQuery. Instructions on how to access this data can be found in the [Connecting](https://developers.stellar.org/docs/data/analytics/hubble/developer-guide/connecting-to-bigquery.md) section.

We will use the [js-stellar-base](https://github.com/stellar/js-stellar-base) library to parse the XDR and employ JavaScript UDFs (User Defined Functions) in BigQuery to apply the transformation to the dataset.

For a deeper understanding, check out the [Medium article](https://medium.com/analytics-vidhya/using-npm-library-in-google-bigquery-udf-8aef01b868f4) on using NPM libraries in Google BigQuery UDFs.

## Step 1: Setting Up JS UDF in BigQuery

To set up the JS UDF in BigQuery, follow these steps:

### 1. Clone the `js-stellar-base` repository

First, clone the repository to your local machine and install the dependencies:

```bash theme={"theme":{"light":"github-light","dark":"github-dark"}}
git clone https://github.com/stellar/js-stellar-base.git
cd js-stellar-base
yarn
yarn build:prod
```

This process will generate the following file in the js-stellar-base/dist/ directory:

* stellar-base.min.js

### 2. Upload the JS file to Google Cloud Storage (GCS)

Once the build process is completed, upload the stellar-base.min.js file to a Google Cloud Storage bucket to be used in the UDF.

## Step 2: Writing the JavaScript Function

Here is an example JavaScript function to extract the fee\_account\_muxed field from the transaction envelope (tx\_meta):

```JavaScript theme={"theme":{"light":"github-light","dark":"github-dark"}}
let tx_meta =
  "AAAABQAAAQAAABYMYQ4r9W/uB9X6q6VU6feQhS2kQoRy9CjvwtYXdPRSih2hZeSSAAAAAAAAAZAAAAACAAAAAJwLL0Ul/CyRZdXuenmdXrzVyX9X56m4kYPYmgppVIj8AAAAZAAF9PwAAAABAAAAAAAAAAAAAAABAAAAAQAAAQAAAFj7+8N85JwLL0Ul/CyRZdXuenmdXrzVyX9X56m4kYPYmgppVIj8AAAAAAAAAADN5igtu93OKhkj2NrSHuPEJktU+0gJ0LiNavJirLAmRwAAAAAF9eEAAAAAAAAAAAFpVIj8AAAAQElnt70S4sGicHyhsN1S29DEREZ7i2HU96+8DfyshlFLCoQudDIxThnVEg2KQDrW61R19M7Ms9IAsznURc5y3wIAAAAAAAAAAaFl5JIAAABAIf9/ecA3id1mbHzJ2S9W5bRVqrjQr/c2+jHEuDNZevt3LDVSc+DmRMYie0eQ+vE7B3D+fRPb9yFzpfx4meTfBg==";

let txe = StellarBase.xdr.TransactionEnvelope.fromXDR(tx_meta, "base64");
let tx = txe.feeBump();
let sourceAccount = StellarBase.encodeMuxedAccountToAddress(
  tx.tx().feeSource(),
);
console.log(sourceAccount);
```

This script will output the `fee_account_muxed` value: `MBX64B6V7KV2KVHJ66IIKLNEIKCHF5BI57BNMF3U6RJIUHNBMXSJEAAACYGGCDRL6UFO2`

## Step 3: Wrapping the JavaScript Function as a UDF

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE TEMP FUNCTION getFeeBumpAccountIfExists(tx_meta STRING)
RETURNS STRING
LANGUAGE js
OPTIONS (
  library=["gs://stellar-test-js-udf/stellar-base.min.js"]  -- Path to JS library in GCS
)
AS r"""
    return StellarBase.encodeMuxedAccountToAddress(
        StellarBase.xdr.TransactionEnvelope.fromXDR(tx_meta, 'base64')
          .feeBump()
          .tx()
          .feeSource()
    );
""";

WITH fee_bump_transactions AS (
    SELECT batch_run_date, transaction_hash, tx_envelope AS tx_meta
    FROM `test_crypto_stellar.history_transactions`
    WHERE batch_run_date BETWEEN DATETIME("2024-07-01") AND DATETIME_ADD("2024-07-20", INTERVAL 1 MONTH)
    AND inner_transaction_hash IS NOT NULL -- filter in fee bump transactions
),
calculated_fee_account AS (
    SELECT batch_run_date, transaction_hash, getFeeBumpAccountIfExists(tx_meta) AS fee_account
    FROM fee_bump_transactions
),
calculated_fee_muxed_account AS (
    SELECT batch_run_date, transaction_hash, fee_account
    FROM calculated_fee_account
    WHERE fee_account LIKE 'M%' -- muxed accounts
)
SELECT batch_run_date, transaction_hash, fee_account AS fee_account_muxed
FROM calculated_fee_muxed_account
```

### Sample Output for the JS UDF

After running the above query, you should receive output similar to the following:

| Row | transaction\_hash | fee\_account\_muxed |
| - | - | - |
| 1 | f5f5b0aaf758896ef8c5b4807f41c77d15c11977eecf2b0e4769d777324a2d11 | MCBD54KAHHA4AK4DOZWOSX5O5OZ4OI54N24QITDSFLPD7EG2WY2AMAAACYGGCDRL6UBUA |
| 2 | a9e49dff6202663633b83f3645fbf8c2cfeb915db99b2b884a86791b9f8eae2f | MBX64B6V7KV2KVHJ66IIKLNEIKCHF5BI57BNMF3U6RJIUHNBMXSJEAAACYGGCDRL6UFO2 |
| 3 | 00dba50c8689477e6990103338a0eb326725e07a7b7ff187359abf11c23c582a | MC5BEU3DCIMHOHRQDVDAPEPZGMBBALPJ3IQY23VTXC3454SQMNWVSAAACYGGCDRL6UX42 |
| 4 | 2e1c53a9fe1d48ddc493febe467178994e669e3eebf3a4cca646b3cb666616de | MAMYAUW45TC54C3QORQP7OOFYKOXCJTXOG2WIV5LP2HDMR67MWP6IAAACYGGCDRL6VCZM |

## Step 4: Updating Column Values Using UDF

You can also use this UDF to update values in a BigQuery table. Here’s an example of how to do this:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE TEMP FUNCTION getFeeBumpAccountIfExists(tx_meta STRING)
RETURNS STRING
LANGUAGE js
OPTIONS (
  library=["gs://stellar-test-js-udf/stellar-base.min.js"]
)
AS r"""
    let txe = StellarBase.xdr.TransactionEnvelope.fromXDR(tx_meta, 'base64');
    let tx = txe.feeBump();
    let sourceAccount = StellarBase.encodeMuxedAccountToAddress(tx.tx().feeSource());
    return sourceAccount
""";

MERGE `crypto_stellar.history_transactions` AS target
USING (
  WITH fee_bump_transactions AS (
    SELECT batch_run_date, transaction_hash, tx_envelope AS tx_meta
    FROM `crypto_stellar.history_transactions`
    WHERE batch_run_date > '2020-08-03'
      AND batch_run_date < '2020-08-05'
      AND inner_transaction_hash IS NOT NULL
  ),
  calculated_fee_account AS (
    SELECT batch_run_date, transaction_hash, getFeeBumpAccountIfExists(tx_meta) AS fee_account
    FROM fee_bump_transactions
  ),
  calculated_fee_muxed_account AS (
    SELECT batch_run_date, transaction_hash, fee_account
    FROM calculated_fee_account
    WHERE fee_account LIKE 'M%' -- muxed accounts
  )
  SELECT batch_run_date, transaction_hash, fee_account AS fee_account_muxed
  FROM calculated_fee_muxed_account
) AS source
ON target.batch_run_date = source.batch_run_date
   AND target.transaction_hash = source.transaction_hash
WHEN MATCHED THEN
  UPDATE SET target.fee_account_muxed = source.fee_account_muxed;
```

This query will update the `fee_account_muxed` field in the crypto\_stellar.history\_transactions table using values calculated from the UDF.

## Conclusion

By following these steps, you can effectively extract and manipulate XDR data fields in BigQuery using JavaScript UDFs, making it easier to process and analyze your Stellar network data. You can also apply bugfix for existing dataset.