Posts

AWS - AWS Transfer for SFTP - Set up SFTP access for S3 using AWS Transfer for SFTP

Image
To Access the AWS S3 bucket through SFTP you can do it now with AWS Transfer for SFTP. Here are the step to configure that. Create the S3 Bucket. Create a IAM Policy by giving access to the S3 bucket created on the step 1 as shown on the screenshot. Create a IAM Role and attach the policy created on the step 2. Go to the URL https://console.aws.amazon.com/transfer/home?region=us-east-1#   Then click on the button  "Create Server".   On the Create Server screen choose the endpoint according to your requirement. If you don't have any VPC configured then choose Public. Then Select the custom host name if you want to give a custom url to your SFTP service. For the identity provider you can select Service managed if you want to manage users within the service OR you can provide custom identity provider URL if you already have. choose a logging role which have access to cloudwatch to log the activity. Then click "Create server" Then it will br...
Post a Comment
Read more

AWS-Python: Fetching credentials stored in AWS Secret Manager with Python

AWS Secret Manager is a great service to keep all the credetial secretly. Anytime you need to access them to use inside your code, you can fetch those through the AWS SDK. AWS SDK need some information to be ready before start you send any request to AWS Secret Manager AWS Access Key AWS Secret Access Key Region Name Secret Name(The name you have given to store your credential in AWS Secret Manager) Here is code snippet that you need to write to retrive the information from AWS Secret Manager. import boto3 import base64 from botocore.exceptions import ClientError def get_secret(): secret_name= "redshift\db\dbname" region_name = "us-east-1" client = boto3.client( 'secretsmanager' , aws_access_key_id = '<aws_access_key_id>' , aws_secret_access_key = '<aws_secret_access_key>' , region_name = '<region_name>' ...
Post a Comment
Read more

SQL: Find Column Name from MS SQL database

Use the following queries to find the column name from the tables from the database. SELECT table_name=sysobjects.name, column_name=syscolumns.name FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.xtype=’U’ AND syscolumns.name like ‘FirstName’ ORDER BY sysobjects.name,syscolumns.colid SELECT table_name=sysobjects.name, column_name=syscolumns.name, datatype=systypes.name, length=syscolumns.length FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON syscolumns.xtype=systypes.xtype WHERE sysobjects.xtype='U' and syscolumns.name='CARDNAME' ORDER BY sysobjects.name,syscolumns.colid
Post a Comment
Read more

LINQ: Using int.TryParse() within a LINQ where clause

I pretty new to LINQ, and I’m keen to get more experience using it, so whenever an opportunity arises I like to try writing LINQ queries. I needed to write a method to extract a comma separated list of numbers from a config file, and return this as List. I was looking at ways to do this using LINQ, but hit a problem. I wanted my LINQ query to filter out any values in the CSV string that could not be parsed as an int, without causing an exception. Using int.TryParse() seemed like a possible solution, but I had problems because the TryParse() method has an out parameter to store the parsed result. I ended up with the code below, which seems to work, but looks very messy, because I think it is parsing the string twice. public static List<int> AuthorisedGroups { get       {             string[] authorisedGroupsStr = ConfigurationManager.AppSettings["AuthorisedGroups"].Split(new char[] { ...
Post a Comment
Read more

LINQ: Implement NOT IN tables with LINQ to Entity

In SQL so many times we need to filter value from one table when the values are not exist on other table. As Below SELECT    GroupID ,    GroupName ,    GroupNumber      FROM  TableA     WHERE  GroupID NOT IN ( SELECT GroupID FROM TableB )   You can do it as below: return   from c in  context . TableA where !db. context . TableB .Any(p => p.GroupID== c.GroupID) select c;
Post a Comment
Read more

LINQ: implement IN tables with LINQ to Entity

In SQL so many times we need to filter value from one table when the values are exist on other table. As Below SELECT    GroupID ,    GroupName ,    GroupNumber ,   FROM    TableA  WHERE    GroupID IN ( SELECT GroupID FROM TableB )   You can do it as below:  var temp = context . TableA           . Where ( x => context . TableB . Any ( y => y . GroupID != x . GroupID ))           . Select ( x => new { GroupID = x . GroupID , GroupName = x . GroupName , GroupNumber = x . GroupNumber }). ToList (); or return DomainContext.TableA.Where(a => DomainContext.TableB.Any(b => b.GroupId == a.GroupId))
Post a Comment
Read more

LINQ: Left Outer Join With LINQ to Entity

In LINQ every join you made is by default Inner Join. To perform the Left Outer Join you can do like below: from mem in ctx.Member join dist in ctx.District on mem.district_id equals dist.district_id into jointDist from dist in jointDist.DefaultIfEmpty() select new StagingClass {  member_id = mem.member_id,  district_name = dist.district_name }
Post a Comment
Read more