search

MetricsDAO / harmony_dbt

DBT Project for the Harmony Blockchain
12 stars 8 forks source link

Table: HRC20 Prices #14

Closed antonyip closed 2 years ago

antonyip commented 2 years ago

Hourly Prices of hrc20 tokens

@drakedanner :D

token_address day_timestamp price_usd
0x.. 1641520410 0.20432847

Problems: Need to think of how to ingest data from blockchain

To Do Next:

drakedanner commented 2 years ago

We could look to bring in something via api from coin gecko (or coin market cap)

In the past, have used both and do something like coalesce(cg.price,cmc.price)

Happy to dig into this deeper and might look to @jfmyers for some guidance

mattromano commented 2 years ago

I might be down to take a stab at this one, I've spent some time playing with the coingecko api w/ python

antonyip commented 2 years ago

I have this really ugly script as a reference...

// Load the Snowflake Node.js driver.
var snowflake = require('snowflake-sdk');

// Create a Connection object that we can use later to connect.
var connection = snowflake.createConnection( {
    account:  process.env.SF_ACCOUNT,
    username: process.env.SF_USERNAME,
    password: process.env.SF_PASSWORD
    }
    );

// Try to connect to Snowflake, and check whether the connection was successful.

connection.connect( 
    function(err, conn) {
        if (err) {
            console.error('Unable to connect: ' + err.message);
            } 
        else {
            console.log('Successfully connected to Snowflake.');
            // Optional: store the connection ID.
            connection_ID = conn.getId();

            var date = new Date();
            var dateTrunc = date.toISOString();
            var dateFinal = dateTrunc;
            console.log(dateFinal);

            var token_address = ""
            var price = 0;

            // https://web3js.readthedocs.io/en/v1.5.2/web3-eth.html#id97
            const Web3 = require('web3');
            let web3 = new Web3('https://api.s0.t.hmny.io/');
            //let web3 = new Web3('http://143.198.97.157:9500/');

            //console.log('web3' + web3);

            //web3.eth.getGasPrice().then(console.log);
            //web3.eth.getCode('0x72cb10c6bfa5624dd07ef608027e366bd690048f').then(console.log)
            /*
            web3.eth.call({
                to: "0x72cb10c6bfa5624dd07ef608027e366bd690048f", // contract address
                data: "0xc6888fa10000000000000000000000000000000000000000000000000000000000000003"
            }).then(console.log)
            */
            var erc20_interface = [    {      "inputs": [        {          "internalType": "string",          "name": "name_",          "type": "string"        },        {          "internalType": "string",          "name": "symbol_",          "type": "string"        }      ],      "stateMutability": "nonpayable",      "type": "constructor"    },    {      "anonymous": false,      "inputs": [        {          "indexed": true,          "internalType": "address",          "name": "owner",          "type": "address"        },        {          "indexed": true,          "internalType": "address",          "name": "spender",          "type": "address"        },        {          "indexed": false,          "internalType": "uint256",          "name": "value",          "type": "uint256"        }      ],      "name": "Approval",      "type": "event"    },    {      "anonymous": false,      "inputs": [        {          "indexed": true,          "internalType": "address",          "name": "from",          "type": "address"        },        {          "indexed": true,          "internalType": "address",          "name": "to",          "type": "address"        },        {          "indexed": false,          "internalType": "uint256",          "name": "value",          "type": "uint256"        }      ],      "name": "Transfer",      "type": "event"    },    {      "inputs": [        {          "internalType": "address",          "name": "owner",          "type": "address"        },        {          "internalType": "address",          "name": "spender",          "type": "address"        }      ],      "name": "allowance",      "outputs": [        {          "internalType": "uint256",          "name": "",          "type": "uint256"        }      ],      "stateMutability": "view",      "type": "function"    },    {      "inputs": [        {          "internalType": "address",          "name": "spender",          "type": "address"        },        {          "internalType": "uint256",          "name": "amount",          "type": "uint256"        }      ],      "name": "approve",      "outputs": [        {          "internalType": "bool",          "name": "",          "type": "bool"        }      ],      "stateMutability": "nonpayable",      "type": "function"    },    {      "inputs": [        {          "internalType": "address",          "name": "account",          "type": "address"        }      ],      "name": "balanceOf",      "outputs": [        {          "internalType": "uint256",          "name": "",          "type": "uint256"        }      ],      "stateMutability": "view",      "type": "function"    },    {      "inputs": [],      "name": "decimals",      "outputs": [        {          "internalType": "uint8",          "name": "",          "type": "uint8"        }      ],      "stateMutability": "view",      "type": "function"    },    {      "inputs": [        {          "internalType": "address",          "name": "spender",          "type": "address"        },        {          "internalType": "uint256",          "name": "subtractedValue",          "type": "uint256"        }      ],      "name": "decreaseAllowance",      "outputs": [        {          "internalType": "bool",          "name": "",          "type": "bool"        }      ],      "stateMutability": "nonpayable",      "type": "function"    },    {      "inputs": [        {          "internalType": "address",          "name": "spender",          "type": "address"        },        {          "internalType": "uint256",          "name": "addedValue",          "type": "uint256"        }      ],      "name": "increaseAllowance",      "outputs": [        {          "internalType": "bool",          "name": "",          "type": "bool"        }      ],      "stateMutability": "nonpayable",      "type": "function"    },    {      "inputs": [],      "name": "name",      "outputs": [        {          "internalType": "string",          "name": "",          "type": "string"        }      ],      "stateMutability": "view",      "type": "function"    },    {      "inputs": [],      "name": "symbol",      "outputs": [        {          "internalType": "string",          "name": "",          "type": "string"        }      ],      "stateMutability": "view",      "type": "function"    },    {      "inputs": [],      "name": "totalSupply",      "outputs": [        {          "internalType": "uint256",          "name": "",          "type": "uint256"        }      ],      "stateMutability": "view",      "type": "function"    },    {      "inputs": [        {          "internalType": "address",          "name": "recipient",          "type": "address"        },        {          "internalType": "uint256",          "name": "amount",          "type": "uint256"        }      ],      "name": "transfer",      "outputs": [        {          "internalType": "bool",          "name": "",          "type": "bool"        }      ],      "stateMutability": "nonpayable",      "type": "function"    },    {      "inputs": [        {          "internalType": "address",          "name": "sender",          "type": "address"        },        {          "internalType": "address",          "name": "recipient",          "type": "address"        },        {          "internalType": "uint256",          "name": "amount",          "type": "uint256"        }      ],      "name": "transferFrom",      "outputs": [        {          "internalType": "bool",          "name": "",          "type": "bool"        }      ],      "stateMutability": "nonpayable",      "type": "function"    }  ]
            var uniswapv2_interface = [{"inputs":[],"payable":false,"stateMutability":"nonpayable","type":"constructor"},{"anonymous":false,"inputs":[{"indexed":true,"internalType":"address","name":"owner","type":"address"},{"indexed":true,"internalType":"address","name":"spender","type":"address"},{"indexed":false,"internalType":"uint256","name":"value","type":"uint256"}],"name":"Approval","type":"event"},{"anonymous":false,"inputs":[{"indexed":true,"internalType":"address","name":"sender","type":"address"},{"indexed":false,"internalType":"uint256","name":"amount0","type":"uint256"},{"indexed":false,"internalType":"uint256","name":"amount1","type":"uint256"},{"indexed":true,"internalType":"address","name":"to","type":"address"}],"name":"Burn","type":"event"},{"anonymous":false,"inputs":[{"indexed":true,"internalType":"address","name":"sender","type":"address"},{"indexed":false,"internalType":"uint256","name":"amount0","type":"uint256"},{"indexed":false,"internalType":"uint256","name":"amount1","type":"uint256"}],"name":"Mint","type":"event"},{"anonymous":false,"inputs":[{"indexed":true,"internalType":"address","name":"sender","type":"address"},{"indexed":false,"internalType":"uint256","name":"amount0In","type":"uint256"},{"indexed":false,"internalType":"uint256","name":"amount1In","type":"uint256"},{"indexed":false,"internalType":"uint256","name":"amount0Out","type":"uint256"},{"indexed":false,"internalType":"uint256","name":"amount1Out","type":"uint256"},{"indexed":true,"internalType":"address","name":"to","type":"address"}],"name":"Swap","type":"event"},{"anonymous":false,"inputs":[{"indexed":false,"internalType":"uint112","name":"reserve0","type":"uint112"},{"indexed":false,"internalType":"uint112","name":"reserve1","type":"uint112"}],"name":"Sync","type":"event"},{"anonymous":false,"inputs":[{"indexed":true,"internalType":"address","name":"from","type":"address"},{"indexed":true,"internalType":"address","name":"to","type":"address"},{"indexed":false,"internalType":"uint256","name":"value","type":"uint256"}],"name":"Transfer","type":"event"},{"constant":true,"inputs":[],"name":"DOMAIN_SEPARATOR","outputs":[{"internalType":"bytes32","name":"","type":"bytes32"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":true,"inputs":[],"name":"MINIMUM_LIQUIDITY","outputs":[{"internalType":"uint256","name":"","type":"uint256"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":true,"inputs":[],"name":"PERMIT_TYPEHASH","outputs":[{"internalType":"bytes32","name":"","type":"bytes32"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":true,"inputs":[{"internalType":"address","name":"","type":"address"},{"internalType":"address","name":"","type":"address"}],"name":"allowance","outputs":[{"internalType":"uint256","name":"","type":"uint256"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":false,"inputs":[{"internalType":"address","name":"spender","type":"address"},{"internalType":"uint256","name":"value","type":"uint256"}],"name":"approve","outputs":[{"internalType":"bool","name":"","type":"bool"}],"payable":false,"stateMutability":"nonpayable","type":"function"},{"constant":true,"inputs":[{"internalType":"address","name":"","type":"address"}],"name":"balanceOf","outputs":[{"internalType":"uint256","name":"","type":"uint256"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":false,"inputs":[{"internalType":"address","name":"to","type":"address"}],"name":"burn","outputs":[{"internalType":"uint256","name":"amount0","type":"uint256"},{"internalType":"uint256","name":"amount1","type":"uint256"}],"payable":false,"stateMutability":"nonpayable","type":"function"},{"constant":true,"inputs":[],"name":"decimals","outputs":[{"internalType":"uint8","name":"","type":"uint8"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":true,"inputs":[],"name":"factory","outputs":[{"internalType":"address","name":"","type":"address"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":true,"inputs":[],"name":"getReserves","outputs":[{"internalType":"uint112","name":"_reserve0","type":"uint112"},{"internalType":"uint112","name":"_reserve1","type":"uint112"},{"internalType":"uint32","name":"_blockTimestampLast","type":"uint32"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":false,"inputs":[{"internalType":"address","name":"_token0","type":"address"},{"internalType":"address","name":"_token1","type":"address"}],"name":"initialize","outputs":[],"payable":false,"stateMutability":"nonpayable","type":"function"},{"constant":true,"inputs":[],"name":"kLast","outputs":[{"internalType":"uint256","name":"","type":"uint256"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":false,"inputs":[{"internalType":"address","name":"to","type":"address"}],"name":"mint","outputs":[{"internalType":"uint256","name":"liquidity","type":"uint256"}],"payable":false,"stateMutability":"nonpayable","type":"function"},{"constant":true,"inputs":[],"name":"name","outputs":[{"internalType":"string","name":"","type":"string"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":true,"inputs":[{"internalType":"address","name":"","type":"address"}],"name":"nonces","outputs":[{"internalType":"uint256","name":"","type":"uint256"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":false,"inputs":[{"internalType":"address","name":"owner","type":"address"},{"internalType":"address","name":"spender","type":"address"},{"internalType":"uint256","name":"value","type":"uint256"},{"internalType":"uint256","name":"deadline","type":"uint256"},{"internalType":"uint8","name":"v","type":"uint8"},{"internalType":"bytes32","name":"r","type":"bytes32"},{"internalType":"bytes32","name":"s","type":"bytes32"}],"name":"permit","outputs":[],"payable":false,"stateMutability":"nonpayable","type":"function"},{"constant":true,"inputs":[],"name":"price0CumulativeLast","outputs":[{"internalType":"uint256","name":"","type":"uint256"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":true,"inputs":[],"name":"price1CumulativeLast","outputs":[{"internalType":"uint256","name":"","type":"uint256"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":false,"inputs":[{"internalType":"address","name":"to","type":"address"}],"name":"skim","outputs":[],"payable":false,"stateMutability":"nonpayable","type":"function"},{"constant":false,"inputs":[{"internalType":"uint256","name":"amount0Out","type":"uint256"},{"internalType":"uint256","name":"amount1Out","type":"uint256"},{"internalType":"address","name":"to","type":"address"},{"internalType":"bytes","name":"data","type":"bytes"}],"name":"swap","outputs":[],"payable":false,"stateMutability":"nonpayable","type":"function"},{"constant":true,"inputs":[],"name":"symbol","outputs":[{"internalType":"string","name":"","type":"string"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":false,"inputs":[],"name":"sync","outputs":[],"payable":false,"stateMutability":"nonpayable","type":"function"},{"constant":true,"inputs":[],"name":"token0","outputs":[{"internalType":"address","name":"","type":"address"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":true,"inputs":[],"name":"token1","outputs":[{"internalType":"address","name":"","type":"address"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":true,"inputs":[],"name":"totalSupply","outputs":[{"internalType":"uint256","name":"","type":"uint256"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":false,"inputs":[{"internalType":"address","name":"to","type":"address"},{"internalType":"uint256","name":"value","type":"uint256"}],"name":"transfer","outputs":[{"internalType":"bool","name":"","type":"bool"}],"payable":false,"stateMutability":"nonpayable","type":"function"},{"constant":false,"inputs":[{"internalType":"address","name":"from","type":"address"},{"internalType":"address","name":"to","type":"address"},{"internalType":"uint256","name":"value","type":"uint256"}],"name":"transferFrom","outputs":[{"internalType":"bool","name":"","type":"bool"}],"payable":false,"stateMutability":"nonpayable","type":"function"}]
            //var jewelContract = new web3.eth.Contract(erc20_interface,'0x72cb10c6bfa5624dd07ef608027e366bd690048f')
            //jewelContract.methods.balanceOf('0x0ba43bae4613e03492e4c17af3b014b6c3202b9d').call().then(console.log);

            var jewelusdc = 0
            var woneusdc =0
            var tearusdc =0
            var goldusdc =0
            var shvasusdc =0

            var jewelusdcSwap = new web3.eth.Contract(uniswapv2_interface,'0xa1221a5bbea699f507cc00bdedea05b5d2e32eba') // jewel-usdc
            jewelusdcSwap.methods.getReserves().call().then((result) => {
                //console.log(result)
                //stealPrice=result;
                var lhs = result._reserve0 / (10**18)
                var rhs = result._reserve1 / (10**6)
                //console.log('lhs: ' + lhs)
                //console.log('rhs: ' + rhs)
                jusdc = 1/(lhs / rhs)
                console.log('jewel-usdc ratio: ' + jusdc)
                jewelusdc =jusdc

                //0x72cb10c6bfa5624dd07ef608027e366bd690048f
                var token_address_jewel = '0x72cb10c6bfa5624dd07ef608027e366bd690048f'
                connection.execute({
                sqlText: "insert into harmony.dev.ant_token_prices values (:1,:2,:3)",
                binds: [token_address_jewel, dateFinal, jewelusdc],
                complete: function(err, stmt, rows) {
                    if (err)
                    {
                        console.log(err);
                        return;
                    }
                    console.log('jewelusdc updated')
                    }
                });

                var stealPrice = {}
                var jewelSwap = new web3.eth.Contract(uniswapv2_interface,'0xeb579ddcd49a7beb3f205c9ff6006bb6390f138f') // wone-jewel
                jewelSwap.methods.getReserves().call().then((result) => {
                    //console.log(result)
                    stealPrice=result;
                    var lhs = result._reserve0 / (10**18)
                    var rhs = result._reserve1 / (10**18)
                    var wonejewel = (lhs / rhs) * jusdc
                    //console.log('lhs: ' + lhs)
                    //console.log('rhs: ' + rhs)
                    console.log('wone to usdc ratio: ' + wonejewel)
                    woneusdc=wonejewel

                    //0xcf664087a5bb0237a0bad6742852ec6c8d69a27a -- wone
                    var token_address_wone = '0xcf664087a5bb0237a0bad6742852ec6c8d69a27a'
                    connection.execute({
                    sqlText: "insert into harmony.dev.ant_token_prices values (:1,:2,:3)",
                    binds: [token_address_wone, dateFinal, woneusdc],
                    complete: function(err, stmt, rows) {
                        if (err)
                        {
                            console.log(err);
                            return;
                        }
                        console.log('woneusdc updated')
                        }
                    });
                })

                //tears
                //0xc79245ba0248abe8a385d588c0a9d3db261b453c
                var tearjewelSwap = new web3.eth.Contract(uniswapv2_interface,'0xc79245ba0248abe8a385d588c0a9d3db261b453c') // tear-jewel
                tearjewelSwap.methods.getReserves().call().then((result) => {
                    //console.log(result)
                    stealPrice=result;
                    var lhs = result._reserve0 / (10**0)
                    var rhs = result._reserve1 / (10**18)
                    var tearjewel = 1/(lhs / rhs) * jusdc
                    //console.log('lhs: ' + lhs)
                    //console.log('rhs: ' + rhs)
                    console.log('tear to usdc ratio: ' + tearjewel)
                    tearusdc=tearjewel

                    //0x24ea0d436d3c2602fbfefbe6a16bbc304c963d04
                    var token_address_tear = '0x24ea0d436d3c2602fbfefbe6a16bbc304c963d04'
                    connection.execute({
                    sqlText: "insert into harmony.dev.ant_token_prices values (:1,:2,:3)",
                    binds: [token_address_tear, dateFinal, tearusdc],
                    complete: function(err, stmt, rows) {
                        if (err)
                        {
                            console.log(err);
                            return;
                        }
                        console.log('tearusdc updated')
                        }
                    });
                })

                //dfkgold
                //0x321eafb0aed358966a90513290de99763946a54b
                var goldjewelSwap = new web3.eth.Contract(uniswapv2_interface,'0x321eafb0aed358966a90513290de99763946a54b') // gold-jewel
                goldjewelSwap.methods.getReserves().call().then((result) => {
                    //console.log(result)
                    stealPrice=result;
                    var lhs = result._reserve0 / (10**3)
                    var rhs = result._reserve1 / (10**18)
                    var goldjewel = 1/(lhs / rhs) * jusdc
                    //console.log('lhs: ' + lhs)
                    //console.log('rhs: ' + rhs)
                    console.log('gold to usdc ratio: ' + goldjewel)
                    goldusdc=goldjewel
                    var token_address_gold = '0x3a4edcf3312f44ef027acfd8c21382a5259936e7'
                    connection.execute({
                    sqlText: "insert into harmony.dev.ant_token_prices values (:1,:2,:3)",
                    binds: [token_address_gold, dateFinal, goldusdc],
                    complete: function(err, stmt, rows) {
                        if (err)
                        {
                            console.log(err);
                            return;
                        }
                        console.log('goldusdc updated')
                        }
                    });
                })

                //shvas
                //0xb270556714136049b27485f1aa8089b10f6f7f57
                var shvasjewelSwap = new web3.eth.Contract(uniswapv2_interface,'0xb270556714136049b27485f1aa8089b10f6f7f57') // shvas-jewel
                shvasjewelSwap.methods.getReserves().call().then((result) => {
                    //console.log(result)
                    stealPrice=result;
                    var lhs = result._reserve0 / (10**0)
                    var rhs = result._reserve1 / (10**18)
                    var shvasjewel = 1/(lhs / rhs) * jusdc
                    //console.log('lhs: ' + lhs)
                    //console.log('rhs: ' + rhs)
                    console.log('shvas to usdc ratio: ' + shvasjewel)
                    shvasusdc=shvasjewel

                    var token_address_shva = '0x66f5bfd910cd83d3766c4b39d13730c911b2d286'
                    connection.execute({
                    sqlText: "insert into harmony.dev.ant_token_prices values (:1,:2,:3)",
                    binds: [token_address_shva, dateFinal, shvasusdc],
                    complete: function(err, stmt, rows) {
                        if (err)
                        {
                            console.log(err);
                            return;
                        }
                        console.log('shva updated')
                        }
                    });
                })

            })

            }
        }
    );

an alternate idea is to just extract the swaps and guess the pricing from there, might be easier.. :P

mattromano commented 2 years ago

Awesome thanks Ant, that is helpful! I will look into swaps as well.

As far as logistics, if I can get the final script to run in the docker image is there like a VM that would ultimately run this via cron jobs outside of dbt? Or would we utilize snowflakes external functions. Was looking into the external functions on snowflake and it looks like there would be some set-up required in AWS based on my 5 minutes of research.

antonyip commented 2 years ago

Awesome thanks Ant, that is helpful! I will look into swaps as well.

As far as logistics, if I can get the final script to run in the docker image is there like a VM that would ultimately run this via cron jobs outside of dbt? Or would we utilize snowflakes external functions. Was looking into the external functions on snowflake and it looks like there would be some set-up required in AWS based on my 5 minutes of research.

No clue about plans for infra, @drakedanner probably has to think about it.. whether be it VM's or something else... My 2 cents is that its probably better/easier to just look at swaps as that just comes from the data we have anyway.

antonyip commented 2 years ago

Closing this issue: You can now get HRC20 prices from INGEST.PUBLIC.SRC_TOKEN_USD_PRICES